sql_create_table

SQL CREATE TABLE Statement

The SQL CREATE TABLE Statement

The CREATE TABLE statement is fundamental in SQL for defining the structure of a new table within your database. Tables are the primary way data is organized, consisting of rows (records) and columns (fields). Each table must have a unique name within its schema.

Think of CREATE TABLE as drawing the blueprint for a spreadsheet before you can enter any data. You decide what each column will be called and what kind of information it will hold.

SQL CREATE TABLE Syntax

CREATE TABLE table_name (
    column_name1 data_type [constraints],
    column_name2 data_type [constraints],
    column_name3 data_type [constraints],
    ...
    [table_constraints]
);

Let’s break down the syntax:

• table_name: The name you choose for your table (e.g., Employees, Products).
• column_name1, column_name2, …: The names for each column in the table (e.g., EmployeeID, ProductName, HireDate).
• data_type: Specifies the kind of data each column can store. Common data types include:
• INT or INTEGER: For whole numbers (e.g., 1, 100, -50).
• VARCHAR(n): For variable-length strings of characters up to a maximum length n (e.g., ‘John Doe’, ‘Product Description’). Use NVARCHAR(n) in some systems like SQL Server for Unicode character support.
• CHAR(n): For fixed-length strings of characters. If the string is shorter than n, it’s often padded with spaces.
• TEXT or CLOB: For very long strings of text (e.g., articles, long comments).
• DECIMAL(p, s) or NUMERIC(p, s): For numbers with a fixed precision (p total digits) and scale (s digits after the decimal point) (e.g., 123.45).
• FLOAT or REAL: For approximate-number data values (floating-point numbers).
• DATE: For dates (e.g., ‘2024-07-15’).
• TIMESTAMP or DATETIME: For date and time values (e.g., ‘2024-07-15 10:30:00’).
• BOOLEAN (or BIT in some systems): For true/false values.
• BLOB or BYTEA: For binary data like images or files.
• constraints (optional): Rules applied to data in a column or table. Common column constraints include:
• NOT NULL: Ensures a column cannot have a NULL (empty) value.
• UNIQUE: Ensures all values in a column are different.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table. A table can have only one primary key.
• FOREIGN KEY: Links a column (or columns) in one table to the primary key in another table, enforcing referential integrity.
• 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 during insertion.

Tip: The exact data types and their names can vary slightly between different database systems (like PostgreSQL, MySQL, SQL Server, Oracle). Always consult your specific RDBMS documentation for precise details. For a general overview, you can search for ‘SQL Data Types Comparison’.

SQL CREATE TABLE Example

Let’s create a table called Employees to store information about company staff. It will have columns for an ID, first name, last name, email, hire date, and department ID.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,  -- Uniquely identifies each employee
    FirstName VARCHAR(100) NOT NULL, -- First name cannot be empty
    LastName VARCHAR(100) NOT NULL,  -- Last name cannot be empty
    Email VARCHAR(255) UNIQUE,       -- Email must be unique for each employee
    HireDate DATE,                   -- Date the employee was hired
    DepartmentID INT,                -- ID of the department they belong to
    Salary DECIMAL(10, 2) CHECK (Salary >= 0) -- Salary must be non-negative
);

In this example:

• EmployeeID is an integer and the PRIMARY KEY for this table. This means each EmployeeID must be unique and not null, serving as the main identifier for an employee.
• FirstName and LastName are variable-length strings up to 100 characters and NOT NULL, meaning they must be provided for every employee.
• Email is a variable-length string up to 255 characters and must be UNIQUE.
• HireDate stores the date of hiring.
• DepartmentID is an integer. We might later make this a FOREIGN KEY referencing a Departments table.
• Salary is a decimal number that can store values like 50000.00, and the CHECK constraint ensures it cannot be negative.

After executing this statement, an empty Employees table structure is created. It’s ready to be populated with data using the INSERT INTO statement.

The empty “Employees” table structure would conceptually look like this (no data yet):

EmployeeID | FirstName | LastName | Email | HireDate | DepartmentID | Salary

Best Practice: Choosing appropriate data types and defining a PRIMARY KEY are crucial for data integrity and performance. Also, consider which columns should not allow NULL values.

Real-world Application: You’d use CREATE TABLE at the beginning of a project to define your database schema, for example, creating tables for Users, Products, Orders, Comments, etc., based on your application’s requirements.