ConstraintsConstraints ------------ Not null Check Default Primary Key Unique Foreign KeyNOT NULL : To create a table with NOT NULL Constraint : sdbtdb=# create table sdbt_nn( id int not null, name text not null, contact real);--describe the table : sdbtdb=# \d sdbt_nn Table "public.sdbt_nn" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | not null | name | text | | not null | contact | real | | |When insert a violated record : insert into sdbt_nn values(101,'test',432134123); insert into sdbt_nn (name,contact) values('test',432134123); ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, test, 4.32134e+08).To drop a not null constraint : alter table sdbt_nn alter column name drop not null;--describe the table : \d sdbt_nn Table "public.sdbt_nn" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | contact | real | | |To add a not null constraint to exist table : Sdbtdb=# alter table sdbt_nn alter column name set not null;--describe the table. Sdbtdb=# \d sdbt_nn Table "public.sdbt_nn" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | not null | name | text | | not null | contact | real | | |CHECK To Create a table with check constraint : sdbtdb=# create table sdbtck( id int not null, name text, sal int check (sal>2000 and sal< 5000));--describe the table. Sdbtdb=# \d sdbtck Table "public.sdbtck" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | sal | integer | | |Check constraints : "sdbtck_sal_check" CHECK (sal > 2000::double precision AND sal < 5000::double precision)When insert a violated record : Sdbtdb=# insert into sdbtck values(101,'ck1test',5000); ERROR: new row for relation "sdbtck" violates check constraint "sdbtck_sal_check" DETAIL: Failing row contains (101, ck1test, 5000). Sdbtdb=# insert into sdbtck values(101,'ck1test',4000);To drop check constraint : Sdbtdb=# alter table sdbtck drop constraint sdbtck_sal_check;--describe the table. Sdbtdb=# \d sdbtck Table "public.sdbtck" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | sal | real | | |To add a check constraint an existing table alter table sdbtck add constraint sdbt_ck check (sal>5000 and sal< 50000);--describe the table. \d sdbtck Table "public.sdbtck" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | sal | real | | | Check constraints : "sdbt_ck" CHECK (sal > 5000::double precision AND sal < 50000::double precision)DEFAULT : To create a table with Default constraint create table codefault( id int not null, name text, state text default 'TN', contact real);--describe the table. \d codefault Table "public.codefault" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+------------ id | integer | | not null | name | text | | | state | text | | | 'TN'::text contact | real | | | When insert support record and violated record. Sdbtdb=# insert into codefault values (105,'test','AP',452452452); Sdbtdb=# insert into codefault(id,name,contact) values (107,'sdnew',5423535234); Sdbtdb=# select * from codefault; id | name | state | contact -----+-------+-------+------------- 105 | test | AP | 4.52452e+08 107 | sdnew | TN | 5.42354e+09UNIQUE : Duplicate value's not allowed Nullable Multi column allow to handle unique constraint for a single table. To create a table with unique constraint : create table sdbt_uq( id int unique, name text, contact real); --describe the table. \d sdbt_uq Table "public.sdbt_uq" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | | name | text | | | contact | real | | | Indexes: "sdbt_uq_id_key" UNIQUE CONSTRAINT, btree (id) Insert records into the table. insert into sdbt_uq values(101,'sweety',23453465); insert into sdbt_uq values(102,'stefy',21453465); insert into sdbt_uq (name, contact)values('sofy',11453465); insert into sdbt_uq (name, contact)values('sabrina',11453465); sdbtdb=# select * from sdbt_uq; id | name | contact -----+---------+------------- 101 | sweety | 2.34535e+07 102 | stefy | 2.14535e+07 | sofy | 1.14535e+07 --null values allowed | sabrina | 1.14535e+07 (4 rows) -- duplicate value not allowed sdbtdb=# insert into sdbt_uq values(102,'swatty',22353465); ERROR: duplicate key value violates unique constraint "sdbt_uq_id_key" DETAIL: Key (id)=(102) already exists. sdbtdb=# insert into sdbt_uq values(103,'swatty',22353465); sdbtdb=# select * from sdbt_uq; id | name | contact -----+---------+------------- 101 | sweety | 2.34535e+07 102 | stefy | 2.14535e+07 | sofy | 1.14535e+07 | sabrina | 1.14535e+07 103 | swatty | 2.23535e+07 (5 rows) Allow to add another unique constraint on the table. sdbtdb=# alter table sdbt_uq add constraint sdbt_con_uq unique(contact); ALTER TABLE --describe the table. sdbtdb=# \d sdbt_uq Table "public.sdbt_uq" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | | name | text | | | contact | real | | | Indexes: "sdbt_con_uq" UNIQUE CONSTRAINT, btree (contact) "sdbt_uq_id_key" UNIQUE CONSTRAINT, btree (id) To drop an Unique Constraint sdbtdb=# alter table sdbt_uq drop constraint sdbt_uq_id_key; sdbtdb=# \d sdbt_uq Table "public.sdbt_uq" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | | name | text | | | contact | real | | | Indexes: "sdbt_con_uq" UNIQUE CONSTRAINT, btree (contact)PRIMARY KEY : Only one primary key constraint allow for one table. Not Null - nullable not accepted. No Duplication data allowed. To create a table with primary key constraint : Sdbtdb=# create table dept_parent( deptid int primary key, deptname text not null, deptstrenght int not null); sdbtdb=# \d dept_parent Table "public.dept_parent" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- deptid | integer | | not null | deptname | text | | not null | deptstrenght | integer | | not null | Indexes: "dept_parent_pkey" PRIMARY KEY, btree (deptid) Primary key allow to insert distinct values only (No duplication) insert into dept_parent values(301,'ACCOUNTS',40); insert into dept_parent values(312,'MANAGER',10); insert into dept_parent values(313,'CLERK',40); sdbtdb=# SELECT * FROM DEPT_PARENT; deptid | deptname | deptstrenght --------+----------+-------------- 301 | ACCOUNTS | 40 312 | MANAGER | 10 313 | CLERK | 40 (3 rows) When insert a duplicate record it reject the record as violated --duplicate record not allowed. sdbtdb=# insert into dept_parent values(313,'STAFF',40); ERROR: duplicate key value violates unique constraint "dept_parent_pkey" DETAIL: Key (deptid)=(313) already exists.When try to insert null values in primary key constraint field, it violates as not-null. --null values not allowed sdbtdb=# insert into dept_parent (deptname,deptstrenght)values('CLERK',40); ERROR: null value in column "deptid" violates not-null constraint DETAIL: Failing row contains (null, CLERK, 40). One table must have only one primary key only. --another primary key not accepted in same table. sdbtdb=# alter table dept_parent add constraint pk primary key(deptstrenght); ERROR: multiple primary keys for table "dept_parent" are not allowedTo drop a primary key constraint : sdbtdb=# alter table dept_parent drop constraint dept_parent_pkey; sdbtdb=# \d dept_parent Table "public.dept_parent" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- deptid | integer | | not null | deptname | text | | not null | deptstrenght | integer | | not null |To add a primary key constraint an existing table : alter table dept_parent add constraint pk primary key(deptid); \d dept_parent Table "public.dept_parent" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- deptid | integer | | not null | deptname | text | | not null | deptstrenght | integer | | not null | Indexes: "pk" PRIMARY KEY, btree (deptid)Foreign Key : To create a table with foreign key sdbtdb=# create table emp_child( empid int, empname text, age int, doj date, sal real, dept_id int references dept_parent(deptid)); sdbtdb=# \d emp_child Table "public.emp_child" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- empid | integer | | | empname | text | | | age | integer | | | doj | date | | | sal | real | | | dept_id | integer | | | Foreign-key constraints : "emp_child_dept_id_fkey" FOREIGN KEY (dept_id) REFERENCES dept_parent(deptid) Insert related record into child table using foreign key sdbtdb=# select * from dept_parent; deptid | deptname | deptstrenght --------+----------+-------------- 301 | ACCOUNTS | 40 312 | MANAGER | 10 313 | CLERK | 40 (3 rows) Sdbtdb=# insert into emp_child values (20123,'simon',25,'02-20-20',30000,301); Sdbtdb=# select * from emp_child; empid | empname | age | doj | sal | dept_id -------+---------+-----+------------+-------+--------- 20123 | simon | 25 | 2020-02-20 | 30000 | 301 (1 row)TO CREATE TABLE WITH NOT NULL FIELD : insert into emp_child values(20153,'siva',25,'04-21-20',30000,301); insert into emp_child values(53123,'madan',25,'07-05-20',25000,312); insert into emp_child values(53023,'kumar',25,'08-05-20',25000,312); sdbtdb=# select * from emp_child; empid | empname | age | doj | sal | dept_id -------+---------+-----+------------+-------+--------- 20123 | simon | 25 | 2020-02-20 | 30000 | 301 20153 | siva | 25 | 2020-04-21 | 30000 | 301 53123 | madan | 25 | 2020-07-05 | 25000 | 312 53023 | kumar | 25 | 2020-08-05 | 25000 | 312 (4 rows) sdbtdb=# select * from dept_parent; deptid | deptname | deptstrenght --------+----------+-------------- 301 | ACCOUNTS | 40 312 | MANAGER | 10 313 | CLERK | 40 sdbtdb=# select * from emp_child; empid | empname | age | doj | sal | dept_id -------+---------+-----+------------+-------+--------- 20123 | simon | 25 | 2020-02-20 | 30000 | 301 20153 | siva | 25 | 2020-04-21 | 30000 | 301 53123 | madan | 25 | 2020-07-05 | 25000 | 312 53023 | kumar | 25 | 2020-08-05 | 25000 | 312Foreign key always check the references primary key, then allow to insert the record. If primary key contain the record in parent table it allow to insert into child table. Otherwise it won’t allow to insert record in child table. sdbtdb=# insert into emp_child values(53053,'vija',25,'08-05-20',25000,412); ERROR: insert or update on table "emp_child" violates foreign key constraint "emp_child_dept_id_fkey" DETAIL: Key (dept_id)=(412) is not present in table "dept_parent". sdbtdb=# select * from dept_parent; deptid | deptname | deptstrenght --------+----------+-------------- 301 | ACCOUNTS | 40 312 | MANAGER | 10 313 | CLERK | 40If you try to remove parent table records, then first remove the depended record in child table. sdbtdb=# delete from dept_parent where deptid=312; ERROR: update or delete on table "dept_parent" violates foreign key constraint "emp_child_dept_id_fkey" on table "emp_child" DETAIL: Key (deptid)=(312) is still referenced from table "emp_child". sdbtdb=# select * from emp_child; empid | empname | age | doj | sal | dept_id -------+---------+-----+------------+-------+--------- 20123 | simon | 25 | 2020-02-20 | 30000 | 301 20153 | siva | 25 | 2020-04-21 | 30000 | 301 53123 | madan | 25 | 2020-07-05 | 25000 | 312 53023 | kumar | 25 | 2020-08-05 | 25000 | 312 (4 rows)TO CREATE TABLE WITH NOT NULL FIELD --if the record exist in child table it won't allow to remove related data in parent table sdbtdb=# delete from dept_parent where deptid=312; ERROR: update or delete on table "dept_parent" violates foreign key constraint "emp_child_dept_id_fkey" on table "emp_child" DETAIL: Key (deptid)=(312) is still referenced from table "emp_child". --remove data from child table then remove from parent table. delete from emp_child where dept_id=312; delete from dept_parent where deptid=312; sdbtdb=# select * from dept_parent; deptid | deptname | deptstrenght --------+----------+-------------- 301 | ACCOUNTS | 40 312 | MANAGER | 10 313 | CLERK | 40 sdbtdb=# select * from emp_child; empid | empname | age | doj | sal | dept_id -------+---------+-----+------------+-------+--------- 20123 | simon | 25 | 2020-02-20 | 30000 | 301 20153 | siva | 25 | 2020-04-21 | 30000 | 301 53123 | madan | 25 | 2020-07-05 | 25000 | 312 53023 | kumar | 25 | 2020-08-05 | 25000 | 312 sdbtdb=# select * from pg_constraint where conname='emp_child_dept_id_fkey'; -[ RECORD 1 ]-+----------------------- conname | emp_child_dept_id_fkey connamespace | 2200 contype | f condeferrable | f condeferred | f convalidated | t conrelid | 16547 contypid | 0 conindid | 16545 confrelid | 16537 confupdtype | a confdeltype | a confmatchtype | s conislocal | t coninhcount | 0 connoinherit | t conkey | {6} confkey | {1} conpfeqop | {96} conppeqop | {96} conffeqop | {96} conexclop | conbin | consrc |Check the constraint infromation sdbtdb=# select conname, contype from pg_constraint; conname | contype ------------------------------+ --------- cardinal_number_domain_check | c yes_or_no_check | c sdbt_ck | c sdbt_con_uq | u pk | p emp_child_dept_id_fkey | f « Previous Next Topic » (Operators) |