DDL

Table of contents

CREATE TABLE
Column/Table Constraints

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
  column1 DATA_TYPE(len) column_constraint,
  column2 DATA_TYPE(len) column_constraint,
  ...
  table_constraint
)

Column/Table Constraints

Column constraints come after each column name and data type. Multiple column constraints can be added to a column (e.g. UNIQUE NOT NULL).

PRIMARY KEY
UNIQUE
NOT NULL

Table constraints come after column definitions, and are usually given constraint names.

PRIMARY KEY
FOREIGN KEY
CHECK
UNIQUE

PRIMARY KEY

For single column primary key:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
)

PostgreSQL’s SERIAL is like INT AUTO_INCREMENT in MySQL.

If you want to use composite keys, you need to use table constraints:

CREATE TABLE users (
  id INT,
  username VARCHAR(50),
  ...
  [CONSTRAINT pk_users]  -- Optional table constraint name
  PRIMARY KEY (id, username)
)

Adding primary key constraint to existing table:

ALTER TABLE users
ADD [CONSTRAINT pk_users] PRIMARY KEY (id);

Or drop:

ALTER TABLE users
DROP CONSTRAINT pk_users;

FOREIGN KEY

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  category_id INT,
  ...
  [CONSTRAINT fk_users_category_id]
    FOREIGN KEY (category_id)
      REFERENCES categories (id)
        [ON DELETE delete_action]
        [ON UPDATE update_action]
)

`ON DELETE` and `ON UPDATE`

ON DELETE and ON UPDATE refers to situations when the referenced column id is deleted or updated in parent table categories.

Available actions are:

NO ACTION: Actually similar to RESTRICT (more details involved). Raises an error if you try to delete or update in parent table.
If no ON DELETE or ON UPDATE is specified, NO ACTION is the default.
SET NULL: Sets foreign key column to NULL if it is nullable.
SET DEFAULT: Sets to default if column has default value.
CASCADE: Removes or updates all rows in this child table accordingly.
Most commonly used in practice, as it preserves referential integrity.
RESTRICT: Rejects the delete or update operation for the parent table.

Again, you can add foreign key constraint to existing table:

ALTER TABLE users
ADD CONSTRAINT fk_users_category_id
FOREIGN KEY (category_id)
REFERENCES categories (id)
ON DELETE CASCADE;

CHECK

General constraint useful when multiple columns are involved:

CREATE TABLE table_name (
  column1 DATA_TYPE,
  column2 DATA_TYPE,
  ...
  [CONSTRAINT constraint_name]
    CHECK (condition expression)
)

But if you only need to check a single column, you can use a column constraint:

CREATE TABLE table_name (
  column1 DATA_TYPE CHECK (condition)
)

UNIQUE

As a column constraint on single column:

CREATE TABLE users (
  username VARCHAR(50) UNIQUE,
)

As a table constraint on multiple columns:

CREATE TABLE users (
  username VARCHAR(50),
  email VARCHAR(50),
  ...
  [CONSTRAINT uq_users_username_email]
    UNIQUE (username, email)
)

NOT NULL

NOT NULL is a column constraint:

CREATE TABLE users (
  username VARCHAR(50) NOT NULL,
)

You can add NOT NULL to existing column:

ALTER TABLE users
ALTER COLUMN username SET NOT NULL;

DEFAULT

The default can either be a value or an expression:

CREATE TABLE users (
  username VARCHAR(50) DEFAULT 'guest',
  last_created VARCHAR(50) DEFAULT NOW(),
  ...
)

DDL