SQL CREATE TABLE Statement

The SQL CREATE TABLE statement can be used to create a table within a SQL database.

Syntax

CREATE TABLE tableName
(
    columnName1 data_type,
    columnName2 data_type,
    columnName3 data_type,
    columnName4 data_type,
    ...
)

Example

CREATE TABLE Employees
(
    employeeID int,
    employeeName varchar(255),
    hireDate datetimestamp
)

Results

`employeeID`	`employeeName`	`hireDate`

The CREATE TABLE statement used in this example created a table with three columns. The first column is used to store integer data. The second column stores string data (of type varchar) up to 255 characters and the third column is used to store date/time information.

After the table has been created, you can use other SQL statements, such as INSERT INTO to populate data into this table. If you need to modify the table after it has been created, you could use the SQL ALTER statement.

When creating a table, you will most likely need to use constraints to limit the type of data that can go into a table. Constraints can be specified when a table is created or after the table is created using the ALTER TABLE statement.

Here is a list of the most common constraints:

`NOT NULL`	Requires the field to contain data. This constraint will not allow `NULL` values.
`PRIMARY KEY`	Uniquely identifies each record in a database table.
`FOREIGN KEY`	A `FOREIGN KEY` in one table points to a `PRIMARY KEY` in another table. It is used to prevent invalid data from being inserted into the `FOREIGN KEY` field as well as actions that would destroy the relationship between the tables.
`IDENTITY`	While not a constraint, `IDENTITY` is commonly used along with `PRIMARY KEY` to auto increment the integer value on `PRIMARY KEY` for new records. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1). Example `IDENTITY (1,1)`, OR `IDENTITY`.
`DEFAULT`	The DEFAULT constraint is used to insert a default value into a column when no value is specified for the field during the inserting of new records.

Example

CREATE TABLE Employees
(
    employeeID int NOT NULL PRIMARY KEY IDENTITY,
    employeeName varchar(255),
    hireDate datetime,
    deptID int FOREIGN KEY REFERENCES Department(Id),
    status varchar(10) DEFAULT 'Unknown'
)