MSDN Documentation

CREATE TABLE

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 type for each column.

Basic Syntax

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

Parameters

• table_name: The name of the table to be created.
• 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 for the data in a column, such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK.

Example: Creating a Simple 'Customers' Table

This example creates a table named 'Customers' with columns for customer ID, first name, last name, and email.

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

Explanation of the Example:

• CustomerID INT PRIMARY KEY AUTO_INCREMENT: Defines an integer column for the customer's ID, which is the primary key for the table. AUTO_INCREMENT ensures that a unique ID is automatically generated for each new record.
• FirstName VARCHAR(50) NOT NULL: Defines a string column for the first name, with a maximum length of 50 characters. NOT NULL means this field cannot be left empty.
• LastName VARCHAR(50) NOT NULL: Similar to FirstName, for the last name.
• Email VARCHAR(100) UNIQUE: Defines a string column for the email address, with a maximum length of 100 characters. The UNIQUE constraint ensures that no two customers can have the same email address.
• RegistrationDate DATE: Defines a column to store the date when the customer registered.

Example: Creating a 'Products' Table with a Foreign Key

This example demonstrates creating a 'Products' table that references a 'Categories' table using a foreign key.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(100) NOT NULL,
    CategoryID INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryName VARCHAR(50) NOT NULL
);

Common Data Types

Common Constraints