Constraints in SQL/PostgreSQL

Constraints in SQL or PostgreSQL is like ‘gate keepers’ which controls the type of data that can go into a table.

Constraints are the rules that enforced on the data columns of a table. This ensures the accuracy and reliability of the data in the database.

It maintains the integrity of data in the table.

Levels of Constraints in SQL

Constraints could be on a column level or a table level:

The column level
The table level

Table Level Constraint

It define constraints separately after the columns defined.
Constraint name must provide, while defining constraints at this level.
Not null constraints can’t define.
We can define composite keys at this level only.

Column Level Constraint

These constraints defined along with the columns. Hence, constraint name is not required.
We can define not null constraints at this level only(NOT NULL constraint can only apply to one column).
Composite keys can’t define in this level.
Column level constraint is more clear syntactically and more meaningful.

Commonly Used Constraints in SQL

Most commonly used SQL constraints are:

NOT NULL: A NOT NULL constraint specifies that no cell value for any row in this column can be blank.
UNIQUE: The UNIQUE constraint specifies that cell value in a column can’t repeat throughout the table.
PRIMARY KEY: A Primary key uniquely identifies each row in a table. It cannot accept null and duplicate data. One or more of the columns of a table can contain a Primary key.
FOREIGN KEY: This is a field which can uniquely identify each row in another table. It creates a relation between two tables. The first table contains a primary key and the second table contains a foreign key.
CHECK: The CHECK constraint is used to ensure that all the records in a certain column follow a specific rule.
DEFAULT: This constraint specifies a default value for the column when no value specified by the user.

Syntax of Constraints in SQL

Below is the syntax to create constraints using CREATE TABLE statement at the time of creating the table.

CREATE TABLE sample_table(
column1 data_type(size) constraint_name,
column2 data_type(size) constraint_name,
column3 data_type(size) constraint_name,
....
);

Let’s understand it with example:

CREATE TABLE CUSTOMERS( 
   ID   INT              NOT NULL, 
   NAME VARCHAR (20)     NOT NULL, 
   AGE  INT              NOT NULL, 
   ADDRESS  CHAR (25) , 
   SALARY   DECIMAL (18, 2) DEFAULT 00.00,        
   PRIMARY KEY (ID) 
);

Dropping Constraints in SQL

The DROP CONSTRAINTS command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.

ALTER TABLE [Your_Schema].[Your_Table_Name] DROP CONSTRAINT [Your_Constraint_Name]

View Constraints in SQL

To display all constraints on a table,

SHOW CREATE TABLE yourTableName;

Constraints enforce limits to the data or type of data that can inserted/updated/deleted from a table. The purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.