Create table + index + primary

[anselme]

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

[rmiao]

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.

[anselme]

thank you rmiao

and what is the difference between :

[id] [int] NOT NULL PRIMARY KEY CLUSTERED

and

[id] [int] IDENTITY(1,1) NOT NULL

[anselme]

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

[rmiao]

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.

[anselme]

[Users]([id_Users]) was the point !

thank you