Constraints

1. Primary Key Constraints

• A primary key is a column or a group of columns used to identify a row uniquely in a table.

• Technically, a primary key constraint is the combination of a not null constraint and a UNIQUE constraint.

• A table can have one and only one primary key. It is a good practice to add a primary key to every table.

• When you add a primary key to a table, PostgreSQL creates a unique B-tree index on the column or a group of columns used to define the primary key.

• Normally, we add the primary key to a table when we define the table’s structure using CREATE TABLE statement.

    CREATE TABLE TABLE (
        column_1 data_type PRIMARY KEY,
        column_2 data_type,
        …
    );

• In case the primary key consists of two or more columns, you define the primary key constraint as follows:

    CREATE TABLE TABLE (
        column_1 data_type,
        column_2 data_type,
        … 
        PRIMARY KEY (column_1, column_2)
    );

• If you don’t specify explicitly the name for primary key constraint, PostgreSQL will assign a default name to the primary key constraint. By default, PostgreSQL uses table-name_pkey as the default name for the primary key constraint.

• In case you want to specify the name of the primary key constraint, you use CONSTRAINT clause as follows:

    CONSTRAINT constraint_name PRIMARY KEY(column_1, column_2,...);

• To add a primary key to an existing table, you can use,

    ALTER TABLE table_name ADD PRIMARY KEY (column_1, column_2);

• To drop a primary key constraint, you can use,

    ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint;

    --Example

    ALTER TABLE products DROP CONSTRAINT products_pkey;

• TO add an auto incrementing by one primary key to an existing table with column name as ID, you can use,

    ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY;

1. Foreign Key

• A foreign key is a column or a group of columns in a table that reference the primary key of another table.

• The table that contains the foreign key is called the referencing table or child table. And the table referenced by the foreign key is called the referenced table or parent table.

• A table can have multiple foreign keys depending on its relationships with other tables.

• The foreign key constraint helps maintain the referential integrity of data between the child and parent tables.

• A foreign key constraint indicates that values in a column or a group of columns in the child table equal the values in a column or a group of columns of the parent table.

• Syntax

    [CONSTRAINT fk_name]
    FOREIGN KEY(fk_columns) 
    REFERENCES parent_table(parent_key_columns)
    [ON DELETE delete_action]
    [ON UPDATE update_action]