MSDN Archive

CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database. It defines the table's name, its columns, and the data types for each column.

Syntax

The basic syntax for the CREATE TABLE statement is:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ....
);

Parameters

• table_name: The name of the table you want to create. Table names must be unique within a schema.
• column1, column2, ...: The names of the columns in the table.
• datatype: Specifies the type of data that can be stored in each column (e.g., INT, VARCHAR(255), DATE, DECIMAL(10, 2)).
• constraints: Optional rules that can be applied to the columns to enforce data integrity. Common constraints include:
  • NOT NULL: Ensures that a column cannot have a NULL value.
  • UNIQUE: Ensures that all values in a column are unique.
  • PRIMARY KEY: A combination of NOT NULL and UNIQUE. Uniquely identifies each row in the table.
  • FOREIGN KEY: Used to link tables together. It ensures that the values in one column match values in another column (usually the primary key) of a different table.
  • CHECK: Ensures that all values in a column satisfy a specific condition.
  • DEFAULT: Sets a default value for a column if no value is specified.

Examples

1. Creating a simple table:

This example creates a table named Customers with an ID, name, and email.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

2. Creating a table with a foreign key:

This example creates an Orders table that references the Customers table using a foreign key.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

3. Creating a table with a CHECK constraint:

This example creates a Products table where the price must be greater than or equal to zero.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) CHECK (Price >= 0)
);