Results 1 to 6 of 6

Thread: Create table + index + primary

  1. #1
    Join Date
    Apr 2006
    Posts
    178

    Create table + index + primary

    for MS SQL 2000
    how can I do this in one time (into the CREATE TABLE)

    CREATE TABLE [dbo].[Users] (
    [id_Users] [int] NOT NULL ,
    [Name] [nvarchar] (100) NULL,
    [Serial] [nvarchar] (100) NULL,
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
    (
    [id_Users]
    ) ON [PRIMARY]


    CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]


    and that one

    CREATE TABLE [dbo].[UsersExtra] (
    [id_Users] [int] NOT NULL
    ) ON [PRIMARY]


    ALTER TABLE [dbo].[UsersExtra] ADD
    CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
    (
    [id_Users]
    ) REFERENCES [Users] (
    [id_Users]
    ) ON DELETE CASCADE


    thank you

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    CREATE TABLE [dbo].[Users] (
    [id_Users] [int] NOT NULL PRIMARY KEY clustered,
    [Name] [nvarchar] (100) NULL,
    [Serial] [nvarchar] (100) NULL unique,
    ) ON [PRIMARY]


    CREATE TABLE [dbo].[UsersExtra] (
    [id_Users] [int] NOT NULL references [users].[id_users] on delete cascade
    ) ON [PRIMARY]


    You can find syntax in books online.
    Last edited by rmiao; 12-17-2006 at 10:02 PM.

  3. #3
    Join Date
    Apr 2006
    Posts
    178
    thank you rmiao

    and what is the difference between :

    [id] [int] NOT NULL PRIMARY KEY CLUSTERED

    and

    [id] [int] IDENTITY(1,1) NOT NULL
    Last edited by anselme; 12-17-2006 at 10:42 PM.

  4. #4
    Join Date
    Apr 2006
    Posts
    178
    i am getting an error

    CREATE TABLE [dbo].[Users] (
    [id_Users] [int] NOT NULL PRIMARY KEY clustered,
    [Name] [nvarchar] (100) NULL
    ) ON [PRIMARY]


    CREATE TABLE [dbo].[UsersExtra] (
    [id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
    ) ON [PRIMARY]



    Msg 1767, Level 16, State 0, Line 50
    Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
    Msg 1750, Level 16, State 0, Line 50

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Typo, should be:

    CREATE TABLE [dbo].[UsersExtra] (
    [id_Users] [int] NOT NULL references [Users]([id_Users]) on delete cascade
    ) ON [PRIMARY]


    When you specify identity for the column, sql will generates value for it.

  6. #6
    Join Date
    Apr 2006
    Posts
    178
    [Users]([id_Users]) was the point !

    thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •