Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Foreign Key -3

PRACTICAL 6.CHILD TABLE CAN HAVE MORE THAN ONE FOREIGN KEY CONSTRAINTS:
  • A Child table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables. 
  • Say you have tables about Sales2,Products2 and Electronics2,Electronics2 table can have  more referencing like sales2 and products2
Parent table_2(Referenced table):
postgres=# CREATE TABLE sales2 (
    sales_id integer PRIMARY KEY,
    price integer
    );

Parent table_2(Referenced table):
postgres=# CREATE TABLE products2 (
    product_name varchar,
    product_category varchar,
    product_id integer PRIMARY KEY 
);


Child table(Referencing table):
postgres=# CREATE TABLE Electronics2 (
    Electronics_name varchar,
    Electroics_id integer REFERENCES products2(product_id),
    Foreign key(Electroics_id) REFERENCES sales2(sales_id) 
);
postgres=# \d sales2
     Table "public.sales2"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 sales_id | integer | not null
 price    | integer | 
Indexes:
    "sales2_pkey" PRIMARY KEY, btree (sales_id)
Referenced by:
    TABLE "electronics2" CONSTRAINT "electronics2_electroics_id_fkey1" FOREIGN KEY (electroics_id) REFERENCES sales2(sales_id)
postgres=# \d products2
             Table "public.products2"
      Column      |       Type        | Modifiers 
------------------+-------------------+-----------
 product_name     | character varying | 
 product_category | character varying | 
 product_id       | integer           | not null
Indexes:
    "products2_pkey" PRIMARY KEY, btree (product_id)
Referenced by:
    TABLE "electronics2" CONSTRAINT "electronics2_electroics_id_fkey" FOREIGN KEY (electroics_id) REFERENCES products2(product_id)
postgres=# \d electronics2
           Table "public.electronics2"
      Column      |       Type        | Modifiers 
------------------+-------------------+-----------
 electronics_name | character varying | 
 electroics_id    | integer           | 
Foreign-key constraints:
    "electronics2_electroics_id_fkey" FOREIGN KEY (electroics_id) REFERENCES products2(product_id)
    "electronics2_electroics_id_fkey1" FOREIGN KEY (electroics_id) REFERENCES sales2(sales_id)
From above example  electroics2 table  in the  column electroics_id  having two referencing table one is  column product_id  in table of  products2 and second  one is column sales_id  in table of  sales2.

--Now let us make some inserting operation  for understanding purposes
Parent1_1 table:
postgres=# insert into sales2 values(1,4000),(2,550),(3,430);
INSERT 0 3

postgres=# select * from sales2;
 sales_id | price 
----------+-------
        1 |  4000
        2 |   550
        3 |   430
(3 rows)
parent_2 table:
postgres=# insert into products2 values('wire','electronics',1),('switch','electronics',2);
INSERT 0 2

postgres=# select * from products2;
 product_name | product_category | product_id 
--------------+------------------+------------
 wire         | electronics      |          1
 switch       | electronics      |          2
(2 rows)
postgres=# insert into electronics2 values('wire',1);                                         
INSERT 0 1
postgres=# insert into electronics2 values('switch',2);
INSERT 0 1

postgres=# insert into electronics2 values('cables',3);
ERROR:  insert or update on table "electronics2" violates foreign key constraint "electronics2_electroics_id_fkey"
DETAIL:  Key (electroics_id)=(3) is not present in table "products2".

postgres=# select * from electronics2;                 
 electronics_name | electroics_id 
------------------+---------------
 wire             |             1
 switch           |             2
(2 rows)

Solution:
product_id 3 is not there in products2 table that is why it shown error. 

postgres=# insert into products2 values('cable','electronics',3);
INSERT 0 1
postgres=# insert into electronics2 values('cables',3);          
INSERT 0 1 
--You cannot delete parent table rows if that rows having any reference,without reference row only can be deleted
postgres=# delete from sales2 where sales_id=3;
ERROR:  update or delete on table "sales2" violates foreign key constraint "electronics2_electroics_id_fkey1" on table "electronics2"
DETAIL:  Key (sales_id)=(3) is still referenced from table "electronics2". 
But you can delete child table rows if that table having whether reference or not
postgres=# delete from electronics2 where electronics_name='wire';
DELETE 1 
PRACTICAL 7.DELETING FOREIGN REFERENCE KEY ROWS:
  • when someone wants to Delete a row of SALES3 table that is still referenced by an PRODUCTS3 table, It disallow to delete. 
  • If someone delete a row of CATEGORY3 table , the PRODUCTS3 are removed as well:
parent table -1:
CREATE TABLE SALES3 (
    sales_id integer PRIMARY KEY,
    price numeric
);
parent table -2:
CREATE TABLE CATEGORY3 (
    category_id integer PRIMARY KEY,
    cat_name text
  
);
Child table: 
CREATE TABLE PRODUCTS3 (
    product_no integer REFERENCES sales3(sales_id) ON DELETE RESTRICT,
    order_id integer REFERENCES category3(category_id) ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
postgres=# \d sales3
     Table "public.sales3"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 sales_id | integer | not null
 price    | numeric | 
Indexes:
    "sales3_pkey" PRIMARY KEY, btree (sales_id)
Referenced by:
    TABLE "products3" CONSTRAINT "products3_product_no_fkey" FOREIGN KEY (product_no) REFERENCES sales3(sales_id) ON DELETE RESTRICT


postgres=# \d category3
     Table "public.category3"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 category_id | integer | not null
 cat_name    | text    | 
Indexes:
    "category3_pkey" PRIMARY KEY, btree (category_id)
Referenced by:
    TABLE "products3" CONSTRAINT "products3_order_id_fkey" FOREIGN KEY (order_id) REFERENCES category3(category_id) ON DELETE CASCADE


postgres=# \d products3
     Table "public.products3"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 product_no | integer | not null
 order_id   | integer | not null
 quantity   | integer | 
Indexes:
    "products3_pkey" PRIMARY KEY, btree (product_no, order_id)
Foreign-key constraints:
    "products3_order_id_fkey" FOREIGN KEY (order_id) REFERENCES category3(category_id) ON DELETE CASCADE
    "products3_product_no_fkey" FOREIGN KEY (product_no) REFERENCES sales3(sales_id) ON DELETE RESTRICT
Insert some valuse:
postgres=# delete from sales3 where price=400;

ERROR:  update or delete on table "sales3" violates foreign key constraint "products3_product_no_fkey" on table "products3"
DETAIL:  Key (sales_id)=(1) is still repostgres=# insert into sales3 values(1,400),(2,500),(3,1000);
INSERT 0 3
postgres=# insert into category3 values(1,'pendrive'),(2,'pen'),(3,'mouse');
INSERT 0 3
postgres=# insert into products3 values(1,1,50),(2,2,73),(3,3,65);          
INSERT 0 3

Parent table-1:
postgres=# select * from sales3;
 sales_id | price 
----------+-------
        1 |   400
        2 |   500
        3 |  1000
(3 rows)

Parent table -2:
postgres=# select * from category3;
 category_id | cat_name 
-------------+----------
           1 | pendrive
           2 | pen
           3 | mouse
(3 rows)

Child table:
postgres=# select * from products3;
 product_no | order_id | quantity 
------------+----------+----------
          1 |        1 |       50
          2 |        2 |       73
          3 |        3 |       65
(3 rows)ferenced from table "products3". 
--Now we are going to delete sales3 table row,Below deleting particular row is referenced by products3
postgres=# delete from sales3 where price=400;

ERROR:  update or delete on table "sales3" violates foreign key constraint "products3_product_no_fkey" on table "products3"
DETAIL:  Key (sales_id)=(1) is still referenced from table "products3". 
--Now we are going to delete category rows this table already having reference with "cascade" option that is why  
postgres=# delete from category3 where category_id=1;
DELETE 1

postgres=# select * from category3;
 category_id | cat_name 
-------------+----------
           2 | pen
           3 | mouse
(2 rows)

postgres=# select * from products3;
 product_no | order_id | quantity 
------------+----------+----------
          2 |        2 |       73
          3 |        3 |       65
(2 rows) 
When deleting a Row, PostgreSQL gives us the following main options: 
  1. DELETE RESTRICT:PostgreSQL does not delete a row in the parent(SALES3) table until all referenced rows in the child(PRODUCTS3)table deleted. To achieve this, we use ON DELETE RESTRICT expression when we define a the foreign key constraint.
  2.  DELETE CASCADE:PostgreSQL will delete all rows in the CHILD(PRODUCTS3) table that are referenced to the rows that are being deleted in the PARENT(CATEGORY) table
  3. NO ACTION:By default. With NO ACTION, PostgreSQL will raise an error if the referencing rows still exist when the constraint is checked.
To add a foreign key constraint to a table:
ALTER TABLE CHILD ADD CONSTRAINT KEY_NMAE FOREIGN KEY (CHILD_COLUMN_1) REFERENCES PARENT (PAREN_COLUMN_1) MATCH FULL;


--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 
----------------------------------+---------+----------+----------------+-------
 products2_pkey                   | p       |    25127 | products2      | 25127
 orders1_product_no_fkey          | f       |    24937 | orders1        | 24937
 orders1_pkey                     | p       |    24937 | orders1        | 24937
 car_sales_id_fkey                | f       |    24955 | car            | 24955
 car_pkey                         | p       |    24955 | car            | 24955
 check_name3                      | c       |    24814 | village        | 24814
 sales1_product_name_key          | u       |    25018 | sales1         | 25018
 sales1_reg_id_key                | u       |    25018 | sales1         | 25018
 sales1_pkey                      | p       |    25018 | sales1         | 25018
 postgres_ord_no_key              | u       |    24715 | postgres       | 24715
 products1_pkey                   | p       |    24929 | products1      | 24929
(10 rows) 






Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

Script to kill ALL IDLE Connection In postgreSQL

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?