PostgreSQL - SPLessons

PostgreSQL Constraint

Chapter 17

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Constraint

PostgreSQL Constraint

shape Description

By using these PostgreSQL Constraint, different number of conditions can be applied on a column. PostgreSQL Constraint setup the RDBMS and execute the integrity of the database automatically, without need to create triggers and rules. PostgreSQL Constraint are utilized to limit the type of information that can be inserted into a table.

PostgreSQL Constraint can be classified into the following types. They are


Unique Constraints

shape Description

By defining a column with unique constraint, then that unique constraint column will not accept duplicate values. i.e, unique constraint will accept a single character or number. It will not accept repeated values in the table.

shape Syntax

create table <table_name>(<column_name1> datatype(size) constraint  type, <column_name2> datatype(size));

Table_name => Any accurate table in the database.

Column_name => The operation that can be perform on a column in the table.

constraint_type => constraint type will specify the type of constraint,that going to be executed in the database.

shape Examples

By viewing the below example,the concept of unique constraint can be understand easily.

SQLDB=# create table student21(stud_id integer unique,stud_name character varying(50),fee integer);
CREATE TABLE
SQLDB=# insert into student21 values(1001,'James',13000);
INSERT 0 1
SQLDB=# insert into student21 values(1002,'Jake',14000);
INSERT 0 1
SQLDB=# insert into student21 values(1003,'Mike',15000);
INSERT 0 1
SQLDB=# insert into student21 values(1003,'Shah',16000);
ERROR:  duplicate key value violates unique constraint "student21_stud_id_key"
DETAIL:  Key (stud_id)=(1003) already exists.
SQLDB=#

In the above example,the unique constraint is applied to column_name stud_id in which only unique stud_id’s  values will be accept,and duplicate stud_id values will not be accepted.

Not Null Constraints

shape Description

By defining a column with not null constraint then that constraint column won’t acknowledge null values.

shape Syntax

create table( datatype(size)constraint_type, datatype(size);
Table_name => Any accurate table.

Column_name => The operation that can be perform on a column in the table.

constraint_type =>constraint type will specify the type of constraint,that going to be executed in the database.

shape Examples

By viewing the below example, the concept of  not null constraint can be easily understand.

SQLDB=# create table Employee2(Emp_id integer not null,Emp_name character varying(50)not null,fee integer);
CREATE TABLE
SQLDB=# insert into Employee2 values(1001,'Jack',15000);
INSERT 0 1
SQLDB=# insert into Employee2 values(null,'Mike',16000);
ERROR:  null value in column "emp_id" violates not-null constraint
DETAIL:  Failing row contains (null, Mike, 16000).

In the above example if not null constraint is applied on a column_name Emp_id and Emp_name, then that column will accepts the values other than null values.

CHECK Constraint

shape Description

By using the SQL CHECK Constraint we can apply our own conditions on a column. And if we want to define a column with check constraint then that check constraint column cannot accept the values that are not satisfying the condition.

shape Syntax

The syntax for check constraint is as follows
Create table <table_name>(<column_name1>datatype (size),<column_name2>datatype(size),<column_name3>datatype(size) constraint constraint name constraint type);

Table name => The accurate table in the database.

Column name => The operation that can be performed on a column.

shape Examples

The below example show the execution of SQL CHECK Constraint.

SQLDB=# CREATE TABLE Remp(Emp_id integer,Ename character varying,salary integer constraint Remp_sal_ck check(sal<10000));
CREATE TABLE
SQLDB=# insert into Remp values (1001,'Jack',8000);
INSERT 0 1
SQLDB=# insert into Remp values (1002,'Mack',null);
INSERT 0 1
SQLDB=# insert into Remp values (1003,'James',12000);
Error(5200):Salary values for Empno(1003) is greater then the assigned value
SQLDB=# select * from Remp;
 Empno    Ename   | salary 
--------+---------+---------
  1     | Jack    |  8000  
  2     | mack    |  null 
(2 rows)

Here in the above example the column name salary is assigned a condition i.e,salary<10000. Then while assigning the value check condition will check the condition and if the salary is null then the check constraint will not check the condition, and if the salary greater then the assigned condition the oracle server execute the logic and null value will be placed in the table.

Primary Key Constraints

shape Description

By defining a column with primary key constraint then that primary key constraint won’t acknowledge invalid and copy values and a table can contain only one primary key.

Primary key=Unique+Not null

Unique constraints can acknowledge null values where as primary key won’t acknowledge null and copy values.

shape Syntax

create table <table_name> (<column_name1>data type(size) constraint type, <column_name2>data type(size),constraint(column_name1);
Table_name => Any accurate table.

Column_name => The operation that can be performed in the column of a table.

constraint_type => constraint type will specify the type of constraint,that going to be executed in the database.

shape Examples

By viewing the below example, the concept of primary key constraint can be easily understand.

SQLDB=# create table item2(item_id integer primary key not null,item_name character varying(50) not null,fee integer);
CREATE TABLE
SQLDB=# insert into item2 values(1001,'Mike',25000);
INSERT 0 1
SQLDB=# insert into item2 values(1002,'Nike',30000);
INSERT 0 1
SQLDB=# insert into item2 values(1002,'Piste',25000);
ERROR:  duplicate key value violates unique constraint "item2_pkey"
DETAIL:  Key (item_id)=(1002) already exists.
SQLDB=# insert into item2 values(null,'Shah',15000);
ERROR:  null value in column "item_id" violates not-null constraint
DETAIL:  Failing row contains (null, Shah, 15000).

In the above example,the column item_id is assigned primary key constraint. Then this primary key constraint will not accept null and duplicate values.

Foreign key constraints

shape Description

When a column is define with foreign key constraint then that foreign key constraint column will accept only the values of given primary key constraint column.

  • A table can have stand out primary key constraint column.
  • A table can have any number of foreign key constraints columns.
  • A table in which primary key constraint is defined is called parent key.
  • A table in which foreign key constraint column is defined is called child table.
  • A parent table can have any number of child tables, But a child table can have only one parent table.
  • Without inserting parent record in parent table user cannot insert child record in child table.
  • Without deleting child record from child table, user cannot delete parent record from parent table.

shape Syntax

Create table <table_name1>(<column_name1> datatype(size), <column_name2>datatype(size), primary key constraint(column_name1));
create table <table_name2>(<column_name1( datatype(size), <column_name2>datatype(size), datatype(size), FOREIGN KEY constraint(column_name1) REFERENCES(column_name1); );

Table_name => Any accurate table.

Column_name => The operation that can be perform on a column in the table.

constraint_type => constraint type will specify the type of constraint, that going to be executed in the database.

shape Examples

By viewing the below example, the concept of foreign key constraint can be easily understand.

SQLDB=# CREATE TABLE Student14(stud_id intger AUTO_INCREMENT,name character varying NOT NULL,age integer NOT NULL,PRIMARY KEY(stud_id));
CREATE TABLE
SQLDB=# insert into Student14 (name,age) values ('mike',25);
INSERT 0 1
SQLDB=# insert into Student14 (name,age) values ('kate',35);
INSERT 0 1
SQLDB=#  select * from Student14;
 stud_id | name | age 
---------+------+------
     1   | mike |  25 
     2   | kate |  35 
(2 rows)
SQLDB=# CREATE TABLE enrol15(rol_no integer NOT NULL AUTO_INCREMENT,stud_id integer NOT NULL,PRIMARY KEY(rol_no),FOREIGN KEY(stud_id)REFERENCES Student14(stud_id));
INSERT 0 1
SQLDB=#mysql> INSERT INTO enrol15 (stud_id) values (1);
INSERT 0 1
SQLDB=# INSERT INTO enrol15 (stud_id) values (2);
INSERT 0 1
SQLDB=# INSERT INTO enrol15 (stud_id) values (3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`employee`.`enrol15`, CONSTRAINT `enrol15_ibfk_1` FOREIGN KEY (`stud_id`)
REFERENCES `student14` (`stud_id`))
SQLDB=# select * from enrol15;
 rol_no | stud_id 
--------+---------
    1   |    1 
    2   |    2 
(2 rows)

In the above example, 2 tables have been has created i.e, studentd14 and enrol15. In student14  table we assign primary key constraint to column stud_id,so no need to assign stud_id values just write name and age values, stud_id values will be taken automatically. In the table eroll15 foreign key is assign to stud_id with reference to primary key. In such case in enroll15 table assigning stud_id values has to satisfy the stud_id of student14 values,if not it will terminate the values.

Summary

shape Key Points

  • PostgreSQL Constraint – Different number of conditions can be applied on a column with constraints
  • Unique Constraints – Unique constraint will not accept duplicate values.
  • Not Null Constraint – Not Null constraint will not accept Null values.
  • Check Constraint – One can apply they own conditions on a column.
  • Primary Key Constraint – Primary key constraint won’t accept null and copied values.
  • Foreign Key Constraint – Foreign key values will accept only the primary key values.