PostgreSQL - data definition

Create database/table

-- create a new database
create database learning;

-- SERIAL: auto increment integer
-- PRIMARY KEY: identify records in a table uniquely
-- NOT NULL: value cannot be NULL
-- UNIQUE: ensures no two rows having duplicate entries in a column
CREATE TABLE account
(
    user_id    SERIAL PRIMARY KEY,
    username   VARCHAR(50) UNIQUE  NOT NULL,
    password   VARCHAR(50)         NOT NULL,
    email      VARCHAR(250) UNIQUE NOT NULL,
    created_on TIMESTAMP           NOT NULL,
    last_login TIMESTAMP
);

CREATE TABLE job
(
    job_id          SERIAL PRIMARY KEY,
    job_name        VARCHAR(50) UNIQUE NOT NULL,
    job_description VARCHAR(250)       NOT NULL
);

-- NOTE: user_id and job_id here cannot use SERIAL as it's auto increment
-- REFERENCES: create foreign keys
CREATE TABLE account_job
(
    user_id INTEGER REFERENCES account(user_id),
    job_id INTEGER REFERENCES job(job_id),
    hire_date TIMESTAMP
);

Modifying table

-- rename table
alter table info
rename to new_info;

-- rename column
alter table new_info
rename column person to new_person;

-- drop constraint
alter table new_info
alter column new_person drop not null;

-- add constraint
alter table new_info
alter column new_person set not null;

-- drop column
alter table info
drop column person;

Constraints

Check Constraints

The CHECK constraint allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.

the key word CHECK followed by an expression in parentheses
comes after the data type, just like the default value definitions; default values and constraints can be listed in any order

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

You can also give the constraint a name so that you can reference it when you need to change it, the name is followed after the CONSTRAINT keyword (not the CHECK keyword).

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

A constraint can also refer to several columns, Column definitions and the constraint definitions can be listed in mixed order:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

The last constraint appears as a separate item in the comma-separated column list, and it's called table constraints since it's written separately from any one column definition.

Column constraints can also be written as table constraints, while the reverse is not necessarily possible.

-- The following 3 definitions are equivalent
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

NOT NULL constraints

simply specify that a column cannot assume the null value
it's always written as a column constraint
is equivalent to CHECK (column_name IS NOT NULL), but more efficient, no explicit constraint name
the order of the constraints doesn't matter

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

The corresponding NULL constraint simply selects the default behavior that the column might be NULL.

Unique Constraints

ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table
will automatically create a unique B-tree index on the column or group of columns listed in the constraint

-- column constraint with name
create table products (
	product_no integer constraint must_be_different  unique,
	name text,
	price numeric
);

-- table constraint with name
create table products (
	product_no integer,
	name text,
	price numeric,
	constraint must_be_different unique (product_no)
);

-- to define a unique constraint for a group of columns using a table constraint
create table products (
	product_no integer,
	name text,
	price numeric,
	unique (product_no, name)
);

By default, two null values are not considered equal in this comparison. The behavior can be changed by adding the clause NULLS NOT DISTINCT:

create table products (
	product_no integer unique nulls not distinct,
	name text,
	price numeric
);

create table products (
	product_no integer,
	name text,
	price numeric,
    unique nulls not distinct (product_no)
);

Primary Keys

indicates that a column, or a group of columns, can be used as a unique identifier for rows in the table
it is a combination of UNIQUE and NOT NULL constraints, so it does not allow NULL values
only one primary key is allowed in a table, but you can have any number of UNIQUE and NOT NULL constraints
it will automatically create a unique B-tree index
it is usually best to follow the convention that every table should have a primary key

create table products (
	product_no integer primary key,
	name text,
	price numeric
);

create table products (
	product_no integer,
	name text,
	price numeric,
	primary key (product_no)
);

-- primary keys can span more than one column
create table products (
	product_no integer,
	name text,
	price numeric,
	primary key (product_no, name)
);

Foreign Keys

a foreign key is a column or group of columns in a relational database table that provides a link between data in two tables
it refers to the field in a table which is the primary key of another table
the number and type of the constraint columns need to match the number and type of the referenced columns.
more than one foreign key is allowed in a table, and the value of foreign keys can be duplicate

-- the referenced table
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

-- the referencing table
CREATE TABLE orders (
	order_id integer PRIMARY KEY,
	product_no integer REFERENCES products (product_no),
	quantity integer
);

-- in absence of a column list, the primary keys of the referenced table is used as the referenced columns
CREATE TABLE orders (
	order_id integer PRIMARY KEY,
	product_no integer REFERENCES products,
	quantity integer
);

-- self-referential foreign key: the foreign references the same table
CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text
);

ON DELETE RESTRICT: prevents the deletion of a referenced row
ON DELETE NO ACTION: if any referencing rows still exist when the constraint is checked, an error is raised (default behavior if nothing is specified)
ON DELETE CASCADE: referencing rows should be automatically deleted when a referenced row is deleted
SET NULL and SET DEFAULT: referencing row(s) will be set to nulls or their default values when a referenced row is deleted

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Difference between ON DELETE RESTRICT and ON DELETE NO ACTION: NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not

The appropriate choice of ON DELETE action depends on what kinds of objects the related tables represent.

when the referencing table represents something that is a component of what is represented by the referenced table and cannot exist independently, then CASCADE could be appropriate.
if the two tables represent independent objects, then RESTRICT or NO ACTION is more appropriate; an application that actually wants to delete both objects would then have to be explicit about this and run two delete commands. In the above example, order items are part of an order, and it is convenient if they are deleted automatically if an order is deleted. But products and orders are different things, and so making a deletion of a product automatically cause the deletion of some order items could be considered problematic.
the actions SET NULL or SET DEFAULT can be appropriate if a foreign-key relationship represents optional information. For example, if the products table contained a reference to a product manager, and the product manager entry gets deleted, then setting the product's product manager to null or a default might be useful.

Reference

ALTER TABLE - SQL Commands

Create database/table​

Modifying table​

Constraints​

Check Constraints​

NOT NULL constraints​

Unique Constraints​

Primary Keys​

Foreign Keys​

Reference​