Constraints
Constraints
------------
Not null
Check
Default
Primary Key
Unique
Foreign Key
NOT 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+09
UNIQUE :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 allowed
To 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 | 312
Foreign 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 | 40
If 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) |