Chapter 6: Designing Tables

Designing tables is one of the most important steps in building a relational database. A good table design enforces clear structure, minimizes redundancy, and provides a foundation for reliable queries. The CREATE TABLE statement defines the structure of each table—its columns, data types, and integrity rules. This chapter explores the statement in depth, explains how constraints ensure valid data, and shows how to create automatically incrementing identifiers.

The CREATE TABLE statement in depth

The CREATE TABLE statement defines a new table and its structure. You specify column names, data types, optional constraints, and sometimes table-level rules. Here is a simple but complete example:

CREATE TABLE employees (
  emp_id    INTEGER PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  position  VARCHAR(50),
  salary    DECIMAL(10,2) CHECK (salary >= 0),
  hired_on  DATE DEFAULT CURRENT_DATE
);

This statement creates a table with five columns, assigns types, and includes constraints to ensure data validity. The database engine records this definition in its system catalog and enforces the rules automatically.

Basic structure

The general form of CREATE TABLE is:

CREATE TABLE table_name (
  column_name data_type [column_constraints],
  ...,
  [table_constraints]
);

Square brackets indicate optional elements. Column-level constraints apply only to one column. Table-level constraints can refer to multiple columns.

Table options

Many databases allow additional options such as specifying storage engines, schema names, or table inheritance (in PostgreSQL). For example:

CREATE TABLE sales.orders (
  order_id    SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total       DECIMAL(8,2)
);

Here, the table resides in the sales schema rather than the default one. The exact syntax for such options differs among engines, but the core concept remains consistent.

Constraints

Constraints define the logical rules that keep data valid. They act as safeguards inside the database engine, preventing bad data from ever being stored. Constraints can be applied per column or across multiple columns at once.

NOT NULL

NOT NULL requires a value in the column. Use it for fields that are always mandatory, such as names, IDs, or timestamps.

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email    VARCHAR(100)
);

Here, username must always contain a value, but email may be NULL.

DEFAULT

DEFAULT assigns a value when none is provided in an INSERT statement. It can be a constant or an expression supported by the database.

CREATE TABLE logs (
  id        INTEGER PRIMARY KEY,
  created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  active    BOOLEAN   DEFAULT TRUE
);

Defaults simplify inserts and maintain consistent initialization of rows.

CHECK

CHECK validates that column values satisfy a logical condition. If the condition fails, the statement is rejected.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  balance    DECIMAL(10,2) CHECK (balance >= 0)
);

Here, the database will not allow negative balances. You can also place CHECK constraints at the table level to involve multiple columns.

UNIQUE

UNIQUE ensures that all values in a column (or combination of columns) are distinct. It can be used alongside a PRIMARY KEY or independently.

CREATE TABLE customers (
  id     INTEGER PRIMARY KEY,
  email  VARCHAR(200) UNIQUE,
  phone  VARCHAR(20)
);

This rule prevents two customers from sharing the same email address.

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each row. It can consist of one or more columns. A table can have only one primary key definition.

CREATE TABLE products (
  product_id INTEGER,
  sku        VARCHAR(20),
  name       TEXT,
  PRIMARY KEY (product_id)
);

When a primary key is defined, the database automatically enforces uniqueness and disallows NULL values in the key columns.

FOREIGN KEY

A FOREIGN KEY establishes a relationship between two tables. It ensures that each referenced value exists in the parent table, preserving referential integrity.

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

Here, if a customer is deleted, their orders will be removed automatically because of ON DELETE CASCADE. The ON UPDATE RESTRICT clause prevents changing a customer ID that still has related orders.

Auto-increment / identity columns

Most tables need a unique numeric key that increases automatically. Rather than calculating it manually, you can use an auto-increment or identity column. This feature generates a new value for each inserted row.

MySQL

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(100)
);

The AUTO_INCREMENT keyword tells MySQL to assign the next available integer automatically. Values remain unique even when rows are deleted.

PostgreSQL

PostgreSQL traditionally used the SERIAL pseudo-type and now supports the standard GENERATED AS IDENTITY clause:

CREATE TABLE employees (
  emp_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   VARCHAR(100)
);

This approach conforms to the SQL standard and provides better control over sequence options.

SQLite

CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY,
  name   TEXT
);

In SQLite, a column defined as INTEGER PRIMARY KEY automatically acts as an alias for the internal row identifier. It increments with each insert.

SQL Server

CREATE TABLE employees (
  emp_id INT IDENTITY(1,1) PRIMARY KEY,
  name   NVARCHAR(100)
);

The parameters in IDENTITY(1,1) specify the starting value and increment. The first inserted row receives 1, the next 2, and so on.

Auto-generated keys are convenient, efficient, and widely supported. Combined with proper constraints, they give every table a reliable way to identify its rows.

By mastering CREATE TABLE and constraints, you can build schemas that maintain data integrity and make future queries predictable and efficient.