Constraints are some
predefined rules applied to columns that are followed to maintain the accuracy
and reliability of data. Data Type is self a constraint in PostgreSQL, you
can't put string data in the INT data type column. The constraints applied to the columns
while creating the table, means constraint create while creating a table.
Constraints can be on column level or table level, column-level constraint
applied on a particular column and table level constraint applied on the whole table.
Constraints available in
PostgreSQL is described below.
1- Primary Key Constraint
We use the primary key
constraint on the column to uniquely identify the row in a database table. It
can't be null and every value of the column should be unique. The primary key
has a crucial role in database schema design, we use them to make relation from
one table to another table, and it becomes a foreign key in other tables.
Example of Primary Key Constraint
We will create an Employee table, column "Id" is Primary Key. Use the following query to
create an Employee table.
CREATE
TABLE public.employee
(
id serial NOT NULL,
fname text COLLATE
pg_catalog."default",
lname text COLLATE
pg_catalog."default",
username text COLLATE
pg_catalog."default",
password text COLLATE
pg_catalog."default",
contact text COLLATE
pg_catalog."default",
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (id)
)
|
CONSTRAINT
employee_primarykey PRIMARY KEY (id) statement is used to
create primary key constraints.
Note: A primary key has one or more columns but a table can have only one primary key.
When more than one column is used to create a primary key, it is called a
composite key.
2- Unique Key Constraint
The unique key constraint
ensures that there should not be the same value for a particular column. It
prevents for the duplicate value for a column, it works much more like Primary
Key but it can have one null value while Primary Key can't have the null value,
for the more update about the difference between Primary key and Unique key the
link.
Example of Unique Key Constraint
Suppose we have an
employee table and the column UserName should have the unique values, for this the scenario we will use Unique Key constraint for the column UserName.
CREATE
TABLE public.employee
(
id serial NOT NULL,
fname text COLLATE
pg_catalog."default",
lname text COLLATE
pg_catalog."default",
username text COLLATE
pg_catalog."default",
password text COLLATE pg_catalog."default",
contact text COLLATE
pg_catalog."default",
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (id),
CONSTRAINT
"uk_employee_UserName" UNIQUE (username)
)
|
CONSTRAINT
"uk_employee_UserName" UNIQUE (username) the statement creates the unique key constraint.
3- Not Null Constraint
Not Null Constraint
ensures that there should not be the null value for the particular column. If
you don't assign Not Null Constraint to the column it by default holds the null
value. If you don't want a particular column to have the null value then you can
use the Not Null Constraint.
Example of Not Null Constraints
In employee table, if we
don't want the column Salary to hold null value then we should use Not Null constraint for this column.
CREATE
TABLE public.employee
(
id serial NOT NULL,
fname text COLLATE
pg_catalog."default",
lname text COLLATE
pg_catalog."default",
username text COLLATE
pg_catalog."default",
password text COLLATE
pg_catalog."default",
contact text COLLATE
pg_catalog."default",
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (id),
CONSTRAINT "uk_employee_UserName"
UNIQUE (username)
)
|
4- Check Constraint
Sometimes we need to save
data with the condition in the table for a particular column, if any condition
is applied to the column then the check constants ensure that the particular
value being entered into a table column must follow the condition. If the
condition is violated by the value being entered in a table, then the record
violates the constraint and is not inserted in the table.
Example of Check Constraint
As per the Child labor act
of the Government of India, no organization can hire a person below the age of
18 years. So if we are creating an employee table, the Age column should have a
condition, the value being inserted must be greater than 18.
CREATE
TABLE public.employee
(
id serial NOT NULL,,
fname text COLLATE
pg_catalog."default",
lname text COLLATE
pg_catalog."default",
username text COLLATE
pg_catalog."default",
password text COLLATE
pg_catalog."default",
contact text COLLATE
pg_catalog."default",
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid bigint,
age integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (id),
CONSTRAINT "uk_UserName" UNIQUE
(username),
CONSTRAINT chk_employee_age CHECK (age
> 18)
)
|
5- Foreign Key Constraint
Foreign Key Constraint
maintains the referential integrity between two tables. It specifies that the
value in a column (or a group of columns) must match the values appearing in
some row of another table.
Example of Foreign Key Constraint
Suppose have an employee
table and employee's address table, address table has a reference of employee
table.
Employee table
CREATE
TABLE public.employee
(
id serial NOT NULL,-- This is primary key
fname text COLLATE
pg_catalog."default",
lname text COLLATE
pg_catalog."default",
username text COLLATE
pg_catalog."default",
password text COLLATE
pg_catalog."default",
contact text COLLATE
pg_catalog."default",
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (id),
CONSTRAINT
"uk_employee_UserName" UNIQUE (username)
)
|
Address table has reference of Employee table(empid)
CREATE
TABLE public.Address
(
id serial NOT NULL,
empid integer REFERENCES
public.employee (id), --foreign key
address1 text,
address2 text,
city text,
country text
)
|
6- Exclusion Constraints
Exclusion Constraint is
not the most common type of constants we use it rarely in database table
schema, but sometimes it may be important in a particular scenario. It ensures
that if any two rows are compared on the specified columns or expressions using
the specified operators, at least one of these operator comparisons will return
false or null. After reading the definition confused? Let me explain, suppose
we design schema for an institute to manage the trainee data such as subject,
class time, daily progress report, etc. Now, suppose you are assigning the
class time to the student, it may be possible for the same student, the user
can assign two classes at the same time. To resolve that types of issue we use
Exclusion Constraint.
Example of Exclusion Constraint
I am creating student
subject period assigning table(Table name is Period).
CREATE
TABLE ClassPeriod
(
id serial not null,
stdid integer not null,
timeperiod tsrange,
EXCLUDE USING gist (
stdid WITH =,
timeperiod WITH &&
)
);
|
Now, try to insert some
records
INSERT
INTO ClassPeriod(stdid,timeperiod)
VALUES(1,'[2019-01-01 14:30,
2019-01-01 15:30)');
INSERT
INTO ClassPeriod(stdid,timeperiod)
VALUES(1,'[2019-01-01 16:30,
2019-01-01 17:30)');
INSERT
INTO ClassPeriod(stdid,timeperiod)
VALUES(1,'[2019-01-01 14:30,
2019-01-01 15:30)');
|
The first and second insert
query will run without error because for the same student there are different
time period is assigned, while the third insert query will return the error as
below.
ERROR: conflicting key value violates exclusion
constraint "classperiod_stdid_timeperiod_excl"
DETAIL: Key (stdid, timeperiod)=(1, ["2019-01-01
14:30:00","2019-01-01 15:30:00")) conflicts with existing key
(stdid, timeperiod)=(1, ["2019-01-01 14:30:00","2019-01-01
15:30:00")).
SQL
state: 23P01
The student and Time range
of third insert query same as first so it creates the violation exclusion
constraint.
Note: Please execute the
command CREATE EXTENSION btree_gist, for the database. This query will install the
btree_gist extension, which defines the exclusion constraints on plain scalar
data types.