CREATE: Databases, Schemas and Tables

Concept

DDL (Data Definition Language) defines the shape of the database. CREATE is where every database begins: a database, then a schema, then tables with typed columns and the constraints that keep the data honest.

Syntax

CREATE DATABASE database_name;

CREATE SCHEMA schema_name;

CREATE TABLE [schema_name.]table_name (
    column_name data_type [column_constraint] [, ...]
    [, table_constraint]
);

Example

CREATE DATABASE IF NOT EXISTS sql_path;
USE sql_path;                                   -- MySQL and SQL Server

CREATE TABLE departments (
    id       INT          NOT NULL,
    name     VARCHAR(60)  NOT NULL,
    location VARCHAR(60)  NULL,
    CONSTRAINT pk_departments PRIMARY KEY (id),
    CONSTRAINT uq_departments_name UNIQUE (name)
);

CREATE TABLE employees (
    id         INT           NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50)   NOT NULL,
    last_name  VARCHAR(50)   NOT NULL,
    email      VARCHAR(120)  NULL,
    dept_id    INT           NULL,
    hire_date  DATE          NOT NULL,
    salary     DECIMAL(10,2) NOT NULL,
    status     VARCHAR(10)   NOT NULL DEFAULT 'active',
    PRIMARY KEY (id),
    UNIQUE KEY uq_employees_email (email),
    CONSTRAINT fk_employees_dept FOREIGN KEY (dept_id)
        REFERENCES departments(id),
    CONSTRAINT ck_employees_salary CHECK (salary >= 0)
);

Explanation

Every part of that statement is doing a job:

• NOT NULL - the column must always have a value.
• DEFAULT 'active' - the value used when the INSERT does not mention the column.
• PRIMARY KEY - identifies a row and, in practice, creates a unique index.
• FOREIGN KEY - dept_id must match an existing departments.id.
• CHECK - a rule the database refuses to break, so no application bug can store a negative salary.
• Named constraints (fk_employees_dept) mean error messages tell you which rule failed, and ALTER TABLE ... DROP CONSTRAINT has something to name.

Dialect notes

Important rules

• A table name must be unique within its schema.
• CREATE TABLE fails if a referenced parent table does not exist yet, so create parents first.
• In MySQL, MariaDB and Oracle, DDL is auto committing: it silently ends the open transaction and cannot be rolled back. In PostgreSQL and SQL Server, DDL is transactional.
• CHECK constraints are enforced from MySQL 8.0.16 and MariaDB 10.2 onward. Older versions parse and ignore them.

Common mistakes

• Creating a child table before its parent and hitting a foreign key error.
• Relying on CHECK in a legacy MySQL version where it was silently ignored.
• Leaving constraints unnamed, then being unable to read or drop them later.
• Adding IF NOT EXISTS everywhere in a migration, which hides the fact that a step already ran with a different definition.

Best practices

• Name every constraint with a readable prefix: pk_, uq_, fk_, ck_.
• Declare NOT NULL unless unknown is genuinely meaningful for that column.
• Put CREATE TABLE statements in version controlled migration files, never type them straight into production.
• Add the constraints at creation time. Adding them later means cleaning up bad rows first.

Practice

1. Write CREATE TABLE projects with an id, a name, a nullable dept_id that references departments, a budget that cannot be negative and a start date that defaults to today.
2. Which of your columns should be NOT NULL, and why is end_date not one of them?
3. Explain what would happen if you ran your statement twice without IF NOT EXISTS.