Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL UNIQUE Constraint -2

PRACTICAL 5.INSERTING OPERATION ON COMPOSITE UNIQUE KEY TABLE(BATCH):
postgres=# insert into batch values(1,'nijam',1);
INSERT 0 1
postgres=# insert into batch values(2,'nijam',5);
INSERT 0 1
postgres=# insert into batch values(3,'nijam',5);
INSERT 0 1
postgres=# insert into batch values(4,'nijam',5);
INSERT 0 1
postgres=# insert into batch values(4,'nijam',8);
INSERT 0 1
postgres=# select * from batch;                                                                          
 batch_id |             batch_name              | members 
----------+-------------------------------------+---------
        1 | nijam                               |       1
        2 | nijam                               |       5
        3 | nijam                               |       5
        4 | nijam                               |       5
        4 | nijam                               |       8
(5 rows)
Note:A table can have at most one UNIQUE  (There can be any number of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the UNIQUE KEY.)
postgres=# insert into batch values(4,'nijam',8);
ERROR:  duplicate key value violates unique constraint "mul_uni_key"
DETAIL:  Key (batch_id, members)=(4, 8) already exists.
Solution:
Postgresql table will have any number of unique and not-null constraints when you inserting the values any one value should be identified as the UNIQUE  values other wise it will throw error look above example.

PRACTICAL 6.UNIQUE CONSTRAINT USING UNIQUE INDEX:
If you want to add a unique constraint to a column or a group of columns using existing unique index. Let’s take a look at the following steps.

step 1.Suppose we have a table named cellphones:
create table cellphones(
id int,
 name VARCHAR (50) NOT NULL,
 phone_model VARCHAR (16) NOT NULL
);
CREATE TABLE
step 2.We create a unique index based on the phone_model column.
postgres=# CREATE UNIQUE INDEX CONCURRENTLY uni_index ON cellphones(phone_model);
CREATE INDEX
step 3. Add a unique constraint to the cellphones table using the "uni_index" index.
postgres=# ALTER TABLE cellphones ADD CONSTRAINT uni_constraint UNIQUE USING INDEX uni_index;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "uni_index" to "uni_constraint"
ALTER TABLE

postgres=# \d cellphones
                                   Table "public.cellphones"
   Column    |         Type          |                        Modifiers                        
-------------+-----------------------+---------------------------------------------------------
 id          | integer               | not null default nextval('cellphones_id_seq'::regclass)
 name        | character varying(50) | not null
 phone_model | character varying(16) | not null
Indexes:
    "cellphones_pkey" PRIMARY KEY, btree (id)
    "uni_constraint" UNIQUE CONSTRAINT, btree (phone_model)
step 4.MONITOR THE PENDING TRANSACTION USING PG_STAT_ACTIVITY:
postgres=# select datid,datname,usename,state_change,state,query_start,waiting from pg_stat_activity;
 datid | datname  | usename  |         state_change          | state  |          query_start          | waiting 
-------+----------+----------+-------------------------------+--------+-------------------------------+---------
 12814 | postgres | postgres | 2017-04-02 12:54:56.761781-04 | active | 2017-04-02 12:54:56.761768-04 | f
 12814 | postgres | postgres | 2017-03-30 11:09:38.073502-04 | idle   | 2017-03-30 11:09:38.03711-04  | f
(2 rows)

Note: ALTER TABLE statement needs an exclusive lock on the table. If you have many pending transactions, it will wait for those to complete before changing the table. You should check the  pg_stat_activity table to see how many pending transactions are in place using the following query:
You should look at the result to find the state column with the value  idle in transaction. Those are the transactions that are pending to complete.

PRACTICAL 7.UNIQUE CONSTRAINT ON MULTIPLE COLUMN USING ALTER TABLE:
postgres=# CREATE TABLE LOCATION (
 No integer,
 village VARCHAR (50) NOT NULL,
 area_code integer
);


postgres=# \d LOCATION
            Table "public.location"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 no        | integer               | 
 village   | character varying(50) | not null
 area_code | integer               | 
--To add a (multicolumn) unique constraint to a table
postgres=# ALTER TABLE LOCATION ADD CONSTRAINT dist_id_zipcode_key UNIQUE (No,area_code);
ALTER TABLE

postgres=# \d LOCATION                                                                   
            Table "public.location"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 no        | integer               | 
 village   | character varying(50) | not null
 area_code | integer               | 
Indexes:
    "dist_id_zipcode_key" UNIQUE CONSTRAINT, btree (no, area_code)
PRACTICAL 8.DROP UNIQUE KEY USING ALTER TABLE STATEMENT:
postgres=# alter table LOCATION drop constraint dist_id_zipcode_key ;
ALTER TABLE

postgres=# \d LOCATION                                               
            Table "public.location"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 no        | integer               | 
 village   | character varying(50) | not null
 area_code | integer   

PRACTICAL 9.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
 postgres_ord_no_key     | u       |    24715 | postgres       | 24715
(17 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