Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: 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
    Dec 2005
    Posts
    266
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are trying to reference a non-existing table, 'Users.id_Users'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    but I have created it just before

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

  5. #5
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    To get back on the first question ...
    You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  6. #6
    Join Date
    Feb 2006
    Posts
    38
    Add Go and try it again

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anselme
    but I have created it just before
    yes, you did

    however, that is not the source of your problem

    you said REFERENCES [Users].[id_Users]

    this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

    for the correct syntax, please see the manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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