PostgreSQL Primary Key -2

PRACTICAL 3:PostgreSQL inserting operation on  primary key table:

postgres=# insert into postgres2 values(1,'postgres','c');  
INSERT 0 1
postgres=# insert into postgres2 values(2,'postgres','c');
INSERT 0 1
postgres=# insert into postgres2 values(4,'postgres','c');
INSERT 0 1
postgres=# insert into postgres2 values(4,'postgres','c');

ERROR:  duplicate key value violates unique constraint "postgres2_pkey"
DETAIL:  Key (order_no)=(4) already exists.                                          

Solution:
primary key will not allow single duplicate value this is why it throw error after that i'm trrying insert the new values it is succesfully inserted.

postgres=# insert into postgres2 values(0,'postgres','c');
INSERT 0 1

PRACTICAL 4.PostgreSQL inserting operation on composite primary key table:

postgres=# insert into postgres4 values(1,'oracle','B',2);
INSERT 0 1
postgres=# insert into postgres4 values(2,'oracle','A',1);
INSERT 0 1
postgres=# insert into postgres4 values(2,'oracle','A',3);
INSERT 0 1
postgres=# insert into postgres4 values(3,'oracle','A',3);
INSERT 0 1
postgres=# insert into postgres4 values(3,'oracle','A',4);
INSERT 0 1
postgres=# insert into postgres4 values(3,'postgres','A',4);ERROR:  duplicate key value violates unique constraint "mulpkey"
DETAIL:  Key (order_no, db_rank)=(3, 4) already exists.                   

solution:
A table can have at most one primary key. (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 primary key.)
so we need to change atleast one column  value which should not be exist in table.

postgres=# insert into postgres4 values(4,'postgres','A',4);
INSERT 0 1

PRACTICAL 5.Create Primary Key in existing table Using ALTER TABLE statement:

postgres=# CREATE TABLE POSTGRES_5(  
order_no integer,   
DB_name character(35),  
DB_grade character(1),
DB_rank integer
);

postgres=# \d postgres_5
      Table "public.postgres_5"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 order_no | integer       | 
 db_name  | character(35) | 
 db_grade | character(1)  | 
 db_rank  | integer       | 

postgres=# alter table postgres_5 add constraint po_pri_key primary key (db_rank);
ALTER TABLE                                                     

postgres=# \d postgres_5                                                        
      Table "public.postgres_5"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 order_no | integer       | 
 db_name  | character(35) | 
 db_grade | character(1)  | 
 db_rank  | integer       | not null
Indexes:
    "po_pri_key" PRIMARY KEY, btree (db_rank)

PRACTICAL 6.Drop Primary Key  Using ALTER TABLE statement:

postgres=# alter table postgres_5 drop constraint po_pri_key ;
ALTER TABLE

postgres=# \d postgres_5                                      
      Table "public.postgres_5"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 order_no | integer       | 
 db_name  | character(35) | 
 db_grade | character(1)  | 
 db_rank  | integer       | not null

postgres=# alter table postgres4 drop constraint mulpkey ;
ALTER TABLE                                    

PostgreSQL constraints view:

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
 code_title              | p       |    24710 | primtab        | 24710
 pkey                    | p       |    24735 | postgres3      | 24735
 con1                    | c       |    24706 | distributorsct | 24706
 postgres1_order_no_key  | u       |    24725 | postgres1      | 24725
 distributors_pkey       | p       |    24690 | distributors   | 24690
 distributors_name_check | c       |    24690 | distributors   | 24690
 postgres_ord_no_key     | u       |    24715 | postgres       | 24715
(10 rows)

Here column "contype" is called constraint type i expalined it following example

c = check constraint
f = foreign key constraint 
p = primary key constraint 
u = unique constraint 
t = constraint trigger
x = exclusion constraint

.