In SQL, the `CREATE TABLE` statement is used to create a new table in a database. This statement defines the table's structure, including its columns, data types, and any constraints or rules that the data must follow. Each column in the table is specified with a name and a data type, and optional constraints can be applied to enforce rules such as uniqueness or referential integrity.
Syntax
The basic syntax of the `CREATE TABLE` statement is as follows:
CREATE TABLE table_name (
column1_name column1_datatype [constraints],
column2_name column2_datatype [constraints],
...
[table_constraints]
);
Components
table_name: The name of the table to be created.
column_name: The name of each column in the table.
column_datatype: The data type for each column (e.g., `INT`, `VARCHAR`, `DATE`).
constraints: Optional rules applied to columns (e.g., `NOT NULL`, `UNIQUE`, `PRIMARY KEY`).
table_constraints: Optional constraints applied to the table as a whole (e.g., `FOREIGN KEY`, `CHECK`).
Example
Here's an example of a `CREATE TABLE` statement to create a simple table for storing employee information:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- A unique identifier for each employee
FirstName VARCHAR(50) NOT NULL, -- The employee's first name, cannot be NULL
LastName VARCHAR(50) NOT NULL, -- The employee's last name, cannot be NULL
HireDate DATE, -- The date the employee was hired
Salary DECIMAL(10, 2) -- The employee's salary, with up to 10 digits and 2 decimal places
);
Description
EmployeeID: An integer that uniquely identifies each employee. It is the primary key, which means each value must be unique and cannot be NULL.
FirstName: A variable character field with a maximum length of 50 characters. It is required (`NOT NULL`).
LastName: Similar to `FirstName`, a variable character field for the employee's last name, also required (`NOT NULL`).
HireDate: A date field to store the date the employee was hired.
Salary: A decimal field for the employee's salary, allowing up to 10 digits in total with 2 digits after the decimal point.
The `CREATE TABLE` statement is fundamental in SQL as it sets up the structure for how data will be stored and managed within the database.