SQL Create Table

Defining a table

A table is where actual data lives. Creating one means describing its structure ahead of time: which columns it has, what kind of value each column stores, and which rules those values must follow. The database uses this definition, called the schema, to validate every row you later insert.

CREATE TABLE employees (
    id        INT,
    full_name VARCHAR(100),
    email     VARCHAR(255),
    hired_on  DATE
);

Each line inside the parentheses declares one column: a column name followed by its data type. INT holds whole numbers, VARCHAR(n) holds text up to n characters, and DATE holds a calendar date. Choosing an appropriate type for each column keeps your data clean and your storage efficient.

Adding constraints

Beyond a data type, each column can carry constraints: rules the database enforces automatically. A PRIMARY KEY marks the column that uniquely identifies each row, NOT NULL forbids missing values, UNIQUE blocks duplicates, and DEFAULT supplies a value when none is given. Constraints move data-quality checks into the database itself so bad rows can never be saved.

CREATE TABLE employees (
    id        INT          PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email     VARCHAR(255) UNIQUE,
    hired_on  DATE         DEFAULT CURRENT_DATE,
    is_active BOOLEAN      DEFAULT TRUE
);

• List one column per line for readability, ending each with a comma except the last.
• Give every table a primary key so each row can be referenced unambiguously.
• Match the data type to the real-world value; do not store numbers or dates as plain text.
• Use NOT NULL for columns that must always have a value.

You can also create a table from the result of a query using CREATE TABLE ... AS SELECT, which copies both the structure and the selected rows. This is handy for making a quick working copy or an archive table.