CREATE TABLE (Transact-SQL)

Syntax

CREATE TABLE
[ database_name.[ schema_name ]. | schema_name. ] table_name
(
    { column_definition [ ...n ] }
    [ , table_constraint [ ...n ] ]
)
[ ; ]

column_definition ::=
    { column_name data_type
        [ NULL | NOT NULL ]
        [ column_default ]
        [ , ... ]
    }

column_default ::=
    { DEFAULT constant_expression | DEFAULT ( constant_expression ) }

table_constraint ::=
    {
        [ CONSTRAINT constraint_name ]
        { { PRIMARY KEY | { UNIQUE [CLUSTERED | NONCLUSTERED] } }
            ( column [ , ...n ] )
      | { FOREIGN KEY
            [ CONSTRAINT constraint_name ]
            REFERENCES referenced_table_name [ ( column [ , ...n ] ) ]
            [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
            [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
        }
      | { CHECK [ CONSTRAINT constraint_name ] }
            ( logical_expression )
    }
    [ , ...n ]
)

Parameters

database_name.[schema_name]. | schema_name.

Specifies the database and schema to which the new table belongs. If database_name is specified, it must be a valid database. If schema_name is specified, it must be a valid schema in the current database. If database_name and schema_name are omitted, the current database and the default schema of the user are used.

table_name

Is the name of the new table. Table names must follow the rules for identifiers. Table names should be unique within a schema.

column_definition

Defines the name, data type, and nullability of columns in the new table.

column_name

Is the name of the column. Column names must be unique within a table. Column names must follow the rules for identifiers.

data_type

Is the data type of the column. This can be a system-supplied data type or a user-defined data type. If a user-defined data type is specified, the user must own the data type or have been granted permission to use it.

NULL | NOT NULL

Specifies whether the column can store NULL values. If this is not specified, NULL is the default.

column_default

Specifies the default value for the column. Default values must be constants (character strings or built-in functions).

table_constraint

Defines a constraint on the table. Constraints are rules that enforce data integrity.

CONSTRAINT constraint_name

Is the name of the constraint. If omitted, SQL Server generates a name for the constraint.

PRIMARY KEY

Uniquely identifies each row in a table. A table can have only one PRIMARY KEY constraint.

UNIQUE

Ensures that all values in a column or a set of columns are unique. A table can have multiple UNIQUE constraints.

CLUSTERED | NONCLUSTERED

Specifies whether the constraint is clustered or nonclustered. If omitted, the default is NONCLUSTERED.

FOREIGN KEY REFERENCES referenced_table_name [( column [ , ...n ] )]

Specifies a constraint that ensures referential integrity by linking data in one table to data in another table. The foreign key is a column or set of columns in one table that refers to the PRIMARY KEY or UNIQUE constraint in another table. The referenced table is called the referenced table.

ON DELETE | ON UPDATE

Specifies the action to take when a row in the parent table is deleted or updated. The options are NO ACTION, CASCADE, SET NULL, and SET DEFAULT.

CHECK

Ensures that the values in a column or set of columns satisfy a specified condition. This is a logical expression that returns TRUE or FALSE.

Examples

Basic Table Creation

CREATE TABLE Production.Product (
    ProductID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NOT NULL,
    ProductNumber varchar(25) NOT NULL,
    Color nchar(15) NULL,
    CONSTRAINT PK_Product_ProductID PRIMARY KEY (ProductID)
);

Table with a Foreign Key

CREATE TABLE Sales.SalesOrderHeader (
    SalesOrderID int IDENTITY(1,1) NOT NULL,
    OrderDate datetime NOT NULL,
    CustomerID int NOT NULL,
    CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY (SalesOrderID),
    CONSTRAINT FK_SalesOrderHeader_Customer FOREIGN KEY (CustomerID)
        REFERENCES Sales.Customer (CustomerID)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);

Table with CHECK Constraint

CREATE TABLE HumanResources.Employee (
    EmployeeID int IDENTITY(1,1) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL,
    Salary decimal(10, 2) NULL,
    CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID),
    CONSTRAINT CK_Employee_Salary CHECK (Salary >= 0)
);