Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL CHECK Constraint

  • CHECK constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.
  • which allows the user to define a condition, that a value entered into a table, has to satisfy before it can be accepted. The CHECK constraint consists of the keyword CHECK followed by parenthesized conditions. The attempt will be rejected when update or insert column values that will make the condition false.
  • Every time you insert a new row, PostgreSQL checks by using CHECK condition. If the condition is true the values will be inserted on particular table  otherwise it would give back an error message and reject the changes. The same process is carried out for the update existing data.
PRACTICAL 1.CREATION OF CHECK CONSRAINTS:
CREATE TABLE products1 (
    product_no integer CHECK(product_no > 0),
    name text,
    price numeric CHECK (price > 0)
);

postgres=# \d products1
     Table "public.products1"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 product_no | integer | 
 name       | text    | 
 price      | numeric | 
Check constraints:
    "products1_price_check" CHECK (price > 0::numeric)
    "products1_product_no_check" CHECK (product_no > 0) 

PRACTICAL 2.CREATION OF CHECK CONSRAINTS WITH CONSTRAINT NAME:
CREATE TABLE products2 (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0),
    CONSTRAINT product_count CHECK (product_no > 0)
);

postgres=# \d products2
     Table "public.products2"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 product_no | integer | 
 name       | text    | 
 price      | numeric | 
Check constraints:
    "positive_price" CHECK (price > 0::numeric)
    "product_count" CHECK (product_no > 0)
There are two CHECK constraints in the products2 table:
First one is the PRICE of the products2 must be greater than 0. If you try to insert someone with PRICE below 0, you will receive an error message.This check will prevent from updating invalid Price in terms of their semantic meanings.
Second one also the product_no  must be greater than 0,which is obvious.

PRACTICAL 3.INSERTING OPERATION ON CHECK CONSTRAINTS:
postgres=# insert into products2 values(1,'COFFEE',8),(2,'MILK',10),(3,'CHICKEN',12);
INSERT 0 3
postgres=# insert into products2 values(1,'COFFEE',8),(2,'MILK',10),(3,'CHICKEN',12);
INSERT 0 3
postgres=# insert into products2 values(0,'COFFEE',8),(2,'MILK',10),(3,'CHICKEN',12);
ERROR:  new row for relation "products2" violates check constraint "product_count"
DETAIL:  Failing row contains (0, COFFEE, 8).

postgres=# insert into products2 values(1,'COFFEE',0),(2,'MILK',10),(3,'CHICKEN',12);
ERROR:  new row for relation "products2" violates check constraint "positive_price"
DETAIL:  Failing row contains (1, COFFEE, 0).
Becouse check constraint column product_no and price  condition should be greater than 0 otherwise you will get error.
postgres=# select * from products2;
 product_no |  name   | price 
------------+---------+-------
          1 | COFFEE  |     8
          2 | MILK    |    10
          3 | CHICKEN |    12
          1 | COFFEE  |     8
          2 | MILK    |    10
          3 | CHICKEN |    12
(6 rows)
PRACTICAL 4.POSTGRESQL CHECK CONSTRAINTS FOR EXISTING TABLES:
postgres=# CREATE TABLE VILLAGE (
  ID integer,
 village_NAME VARCHAR (50),
 area_code integer
);

postgres=# \dt village 
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | village | table | postgres
(1 row)
To add a (multicolumn) unique constraint to VILLAGE TABLE:
postgres=# ALTER TABLE VILLAGE ADD CONSTRAINT CHECK_NAME1 CHECK(ID>10);
ALTER TABLE
Change the column area_code data type from integer to varchar becouse we need to set (char_length(AREA_CODE) = 5)
postgres=# alter table village alter column  area_code type varchar;
ALTER TABLE
postgres=# ALTER TABLE VILLAGE ADD CONSTRAINT CHECK_NAME2 CHECK (char_length(AREA_CODE) = 5);
ALTER TABLE

postgres=# \d village
              Table "public.village"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 id           | integer               | 
 village_name | character varying(50) | 
 area_code    | character varying     | 
Check constraints:
    "check_name1" CHECK (id > 10)
    "check_name2" CHECK (char_length(area_code::text) = 5)
Here id should be greater than 10 and area_code should be equal to 5 character otherwise you will receive error from following example i explained.
postgres=# insert into village values(11,'COFFEE','65483');
INSERT 0 1
postgres=# insert into village values(11,'COFFEE','6548'); 
ERROR:  new row for relation "village" violates check constraint "check_name2"
DETAIL:  Failing row contains (11, COFFEE, 6548).

PRACTICAL 5.DROPPING CHECK CONSTRAINT USING ALTER TABLE STATEMENT:
postgres=# alter table VILLAGE drop constraint check_name1 ;
ALTER TABLE
postgres=# \d village                                       
              Table "public.village"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 id           | integer               | 
 village_name | character varying(50) | 
 area_code    | character varying     | 
Check constraints:
    "check_name2" CHECK (char_length(area_code::text) = 5)

PRACTICAL 6.RENAME CONSTRAINT:
postgres=# ALTER TABLE VILLAGE RENAME CONSTRAINT check_name2 TO check_name3;
ALTER TABLE
                                                                                               
postgres=# \d village                                                                                               
              Table "public.village"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 id           | integer               | 
 village_name | character varying(50) | 
 area_code    | character varying     | 
Check constraints:
    "check_name3" CHECK (char_length(area_code::text) = 5)

PRACTICAL 7.POSTGRESQL CONSTRAINT VIEWS:
if you want see all constraint name and table name(relname) you need to join pg_stat_all_tables and pg_constraint views
postgres=# select a.conname,a.contype,a.conrelid,b.relname,b.relid from pg_constraint a,pg_stat_all_tables b where a.conrelid=b.relid;
          conname           | contype | conrelid |    relname     | relid 
----------------------------+---------+----------+----------------+-------
 prikey                     | p       |    24676 | films          | 24676
 production                 | u       |    24697 | k              | 24697
 distributorc_did_check     | c       |    24702 | distributorc   | 24702
 uni_key                    | u       |    24761 | india          | 24761
 code_title                 | p       |    24710 | primtab        | 24710
 students_student_id_key    | u       |    24753 | students       | 24753
 pkey                       | p       |    24735 | postgres3      | 24735
 uni_constraint2            | u       |    24784 | mobiles        | 24784
 mobiles_pkey               | p       |    24784 | mobiles        | 24784
 uni_constraint             | u       |    24773 | cellphones     | 24773
 cellphones_pkey            | p       |    24773 | cellphones     | 24773
 con1                       | c       |    24706 | distributorsct | 24706
 postgres1_order_no_key     | u       |    24725 | postgres1      | 24725
 mul_uni_key                | u       |    24766 | batch          | 24766
 distributors_pkey          | p       |    24690 | distributors   | 24690
 distributors_name_check    | c       |    24690 | distributors   | 24690
 products1_price_check      | c       |    24798 | products1      | 24798
 products1_product_no_check | c       |    24798 | products1      | 24798
 product_count              | c       |    24806 | products2      | 24806
 positive_price             | c       |    24806 | products2      | 24806
 check_name2                | c       |    24814 | village        | 24814
 postgres_ord_no_key        | u       |    24715 | postgres       | 24715
(22 rows)


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL