CREATE TABLE (Transact-SQL)

Creates a table in the current database.

Syntax

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

-- column_definition
column_name [ data_type ]
    [
        [ DEFAULT constant ]
        | [ IDENTITY [ ( seed , increment ) ] ]
        | [ ROWGUIDCOL ]
        | [  ]
    ]
    [ NULL | NOT NULL ]
    [  ]

-- table_constraint
[ CONSTRAINT  ]
{
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        (  [ ASC | DESC ] [ ,...n ] )
    | CHECK (  )
    | DEFAULT constant
    | FOREIGN KEY [ REFERENCES referenced_table [ ( ref_column [ ,...n ] ) ] ]
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
        [ NOT FOR REPLICATION ]
    | REFERENCES completed_table [ ( completed_column [ ,...n ] ) ]
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
        [ NOT FOR REPLICATION ]
}

-- column_constraint
[ CONSTRAINT  ]
{ PRIMARY KEY | UNIQUE | PRIMARY KEY | UNIQUE | CHECK | DEFAULT | FOREIGN KEY }

-- column_index
[Index index_name [ ,...n ] ]

-- ref_column
column_name

-- search_condition
search_condition

-- column_id
column_name

-- completed_table
table_name

-- completed_column
column_name

Description

The CREATE TABLE statement is used to create a new table in a SQL Server database. Tables are the fundamental objects for storing data in a relational database. You define the structure of the table by specifying its name, column names, data types for each column, and any constraints that apply to the data.

Table Elements

Column Definitions

Table Constraints

Permissions

Requires CREATE TABLE permission in the database and ALTER SCHEMA permission on the schema where the table is being created.

Examples

Basic Table Creation

Create a simple table named Products with an identity primary key and two text columns.

CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Description VARCHAR(500)
);

Table with Constraints

Create a Customers table with a primary key, a unique constraint on email, and a check constraint on the customer's age.

CREATE TABLE Customers (
    CustomerID INT IDENTITY(100,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18 AND Age <= 120),
    RegistrationDate DATETIME DEFAULT GETDATE()
);

Table with Foreign Key

Create an Orders table that references the Customers table using a foreign key.

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME DEFAULT GETDATE(),
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerID)
        REFERENCES Customers(CustomerID)
        ON DELETE CASCADE
);
Note: When creating a foreign key, the referenced column in the parent table must be a primary key or have a unique constraint.
Tip: Use meaningful names for your tables, columns, and constraints to improve code readability and maintainability.
Warning: Be careful when using ON DELETE CASCADE, as it can lead to unintended deletion of related records if not managed properly.

See Also