Wednesday, June 9, 2010

T-SQL command to CREATE a Table

Below is the CREATE TABLE - SQL Command:
CREATE TABLE [TableName]
(
   [Field Name1] [dataType] [(nFieldWidth [, nPrecision])] [NULL | NOT NULL]
   [CHECK Expression]
   [DEFAULT Expression]
   [PRIMARY KEY | UNIQUE]
   [REFERENCES TableName2 (FieldsName)]
   [, FieldName2 ... ]
) ON [PRIMARY]

Below is the example of Employee table:
CREATE TABLE [Employee]
(
   [EmployeeID] [int] NOT NULL PRIMARY KEY,
   [FirstName] [varchar](30) NOT NULL,
   [MiddleName] [varchar](30) NULL,
   [LastName] [varchar](30) NOT NULL,
   [DateOfBirth] [datetime] NOT NULL CHECK ([DateOfBirth]< DateAdd(yy,-20,GETDATE())),
   [DateOfJoining] [datetime] NOT NULL,
   [DeptNo] [smallint] NOT NULL REFERENCES [Department] ([DeptNo]),
   [Manager] [int] NULL REFERENCES [Employee] ([EmployeeID]),
   [Gender] [CHAR](1) NOT NULL DEFAULT ('M')
) ON [PRIMARY]

No comments:

Post a Comment