Skip to content

Constraints

Primary Key

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

  • A table can have one and only one primary key but the primary key can consists of two or more columns.

  • 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.

  • Creating Primary Key on Table Creation:

Create table post( id serial primary key, title char(50) not null);

Create table post( id serial not null, no int not null, title char(50), primary key (id, no));

  • Creating Primary Key on Altering Table

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

Alter table post Add primary key (id);

  • Removing Primary key from table ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint; alter table post drop constraint post_pkey;

Foriegn 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.

  • 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.

  • Syntax:

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

  • PostgreSQL supports the following actions:

  • SET NULL:

    • The SET NULL automatically sets NULL to the foreign key columns in the referencing rows of the child table when the referenced rows in the parent table are deleted.
  • SET DEFAULT:
    • The ON DELETE SET DEFAULT sets the default value to the foreign key column of the referencing rows in the child table when the referenced rows from the parent table are deleted.
  • RESTRICT
    • RESTRICT prevents deletion of a referenced row.
  • NO ACTION:
    • NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised.

  • CASCADE:

    • The ON DELETE CASCADE automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted.
    • In practice, the ON DELETE CASCADE is the most commonly used option.

  • Example:

``` DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS contacts;

CREATE TABLE customers( customer_id INT GENERATED ALWAYS AS IDENTITY, customer_name VARCHAR(255) NOT NULL, PRIMARY KEY(customer_id) );

CREATE TABLE contacts( contact_id INT GENERATED ALWAYS AS IDENTITY, customer_id INT, contact_name VARCHAR(255) NOT NULL, phone VARCHAR(15), email VARCHAR(100), PRIMARY KEY(contact_id), CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id) ); ```

Check

  • A CHECK constraint is a kind of constraint that allows you to specify if values in a column must meet a specific requirement.

  • The CHECK constraint uses a Boolean expression to evaluate the values before they are inserted or updated to the column.

  • If the values pass the check, PostgreSQL will insert or update these values to the column. Otherwise, PostgreSQL will reject the changes and issue a constraint violation error.

  • Adding check while creating new table:

DROP TABLE IF EXISTS employees; CREATE TABLE employees ( id SERIAL PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), birth_date DATE CHECK (birth_date > '1900-01-01'), joined_date DATE CHECK (joined_date > birth_date), salary numeric CHECK(salary > 0) );

  • Adding check for existing table:

ALTER TABLE prices_list ADD CONSTRAINT valid_range_check CHECK (valid_to >= valid_from);

Unique

  • PostgreSQL UNIQUE constraint to make sure that values stored in a column or a group of columns are unique across rows in a table.

  • Sometimes, you want to ensure that values stored in a column or a group of columns are unique across the whole table such as email addresses or usernames.

  • PostgreSQL provides you with the UNIQUE constraint that maintains the uniqueness of the data correctly.

  • When a UNIQUE constraint is in place, every time you insert a new row, it checks if the value is already in the table. It rejects the change and issues an error if the value already exists. The same process is carried out for updating existing data.

  • When you add a UNIQUE constraint to a column or a group of columns, PostgreSQL will automatically create a unique index on the column or the group of columns.

  • Example:

CREATE TABLE person ( id SERIAL PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), email VARCHAR (50) UNIQUE );

CREATE TABLE table ( c1 data_type, c2 data_type, c3 data_type, UNIQUE (c2, c3) );

Null

  • In database theory, NULL represents unknown or information missing. NULL is not the same as an empty string or the number zero.

  • NULL is very special. It does not equal anything, even itself.

  • The expression NULL = NULL returns NULL because it makes sense that two unknown values should not be equal.

  • To check if a value is NULL or not, you use the IS NULL boolean operator.

CREATE TABLE invoices( id SERIAL PRIMARY KEY, product_id INT NOT NULL, qty numeric NOT NULL CHECK(qty > 0), net_price numeric CHECK(net_price > 0) );