PostgreSQL Constraints
Constraints in PostgreSQL:
Introduction
Postgres constraints are used to define rules for columns in a database table. They ensure that no invalid data is entered into the database.
There are different types of constraints that can be used with Postgres.
Primary Key Constraints
A Primary Key is used to identify each row of a table uniquely. The column that participates in the primary key is known as the primary key .
A table can have zero or one primary key. It cannot have more than 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 group of column used to define the primary key.
Technically , a primary key constraint is the combination of a NOT NULL constraint and a UNIQUE constraint .
Syntax of foreign key constraints :
CREATE TABLE Child_table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
...,
) ;
Example of Primary Key constraint :
Identify the student's id as the primary key of the authors table:
CREATE TABLE students (
id INT PRIMARY KEY ,
name TEXT NOT NULL
);
Foreign Key Constraints
A foreign key constraints specifies that the values in a column must match the values appearing in a row of another table. Foreign key constraints are used to create relationships between tables.
Syntax of foreign key constraints :
CREATE TABLE Child_table_name (
column1 datatype,
column2 datatype,
...,
CONSTRAINT fk_name FOREIGN KEY ( FOREIGN_KEY_COLUMN )
REFERENCES Parent_table_name. ( PRIMARY_KEY_COLUMN )
) ;
Example of foreign key constraints:
Define the student_id in the articles table as a foreign key to the id column in the authors table:
CREATE TABLE departments (
id SERIAL PRIMARY KEY ,
dept_name TEXT NOT NULL ,
);
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY ,
emp_name TEXT NOT NULL ,
FOREIGN KEY ( dept_id ) REFERENCES departments ( id )
) ;
In PostgreSQL, Foreign keys come with several constrains that govern how changes in the parent table affect the child table. These constrains can be set when creating or alerting a table.
ON DELETE CASCADE : It automatically deletes any rows in the child table when the corresponding row in the parent table deleted.
ON DELETE SET NULL : It sets the foreign key value in the child table to NULL when the corresponding row in the parent table is deleted.
ON UPDATE CASCADE : It updates the foreign key in the child table when the corresponding primary key in the parent table is updated.
Important Points About Foreign Key Constrains in PostgreSQL
Foreign Key constrains enforce referential integrity between tables by ensuring that a column or a group of column in the child table matches values in the parent table.
A single table can have multiple foreign keys , each establishing a relationship with different parent tables.
PostgreSQL supports several actions that can be taken when the referenced row in the parent table is deleted or updated a name for the foreign key constrains using the CONSTRAINT keyword . If omitted , PostgreSQL assigns an auto-generated name.
If ON DELETE or ON UPDATE actions are not specified , the default behaviour is NO ACTION.
Not Null Constraints
A Not Null constrains allows you to specify that a column's value cannot be null.
Syntax of NOT NULL Constrains :
CREATE TABLE table_name (
column1 datatype,
...,
CONSTRAINT constraint_name NOT NULL
) ;
Example of NOT NULL Constraints :
Validate that student's name cannot be null:
CREATE TABLE students (
id SERIAL PRIMARY KEY ,
name TEXT NOT NULL
);
Unique Constrains
Unique constrains prevent database entries with a duplicate value of the respective column.
Syntax of Unique Constrains :
CREATE TABLE table_name (
column1 datatype,
...,
CONSTRAINT constraint_name UNIQUE
) ;
Example of UNIQUE constraints :
Validate that students email is unique:
CREATE TABLE students (
id SERIAL PRIMARY KEY ,
name TEXT NOT NULL ,
email TEXT UNIQUE
) ;
Check Constraints
Check constraints allows you to specify a Boolean expression for one or several columns. This Boolean expression must be satisfied ( equal to true ) by the column value for the object to be interested.
Syntax of CHECK Constrains :
CREATE TABLE table_name (
column1 datatype,
...,
CONSTRAINT constraint_name CHECK (condition)
) ;
In this syntax:
First, specify the constraint name after the CONSTRAINT keyword. This is optional. If you omit it, PostgreSQL will automatically generate a name for the CHECK constraint.
Second, define a condition that must be satisfied for the constraint to be valid.
Example of CHECK Constrains :
Validate that employees rating is between 1 and 10:
CREATE TABLE employees (
id SERIAL PRIMARY KEY ,
name TEXT NOT NULL ,
rating INT NOT NULL CHECK ( rating > 0 AND rating < = 10 )
);
Adding CHECK constraints to tables
To add CHECK constrains to as exiting table , you use the ALTER TABLE .....ADD CONSTRAINS statement:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK ( condition ) ;
Removing CHECK constraints
To drop CHECK constrains , you use the ALTER TABLE ..... DROP CONSTRAINTS statement :
ALTER TABLE table_name
DROP CONSTRAINT constraint_name ;
