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)