PostgreSQL Primary Key -2
PRACTICAL 3:PostgreSQL inserting operation on primary key table:
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.
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 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
.
Comments
Post a Comment