PostgreSQL Foreign Key -2

In this tutorial, I will teach you about PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints.
Parent table:
the table to which the foreign key references is called referenced table or parent table.
Child table:
The table that contains the foreign key is called referencing table or child table. 

• A foreign key is defined in a table that refers to the primary key of the other table.
• the number and type of the constrained (Child table) columns need to match the number and type of the (parent table)referenced columns.We say that a foreign key constraint maintains referential integrity between child and parent tables.
• A table can have more than one foreign key constraint depending on its relationships with other tables.

SYNTAX:

CREATE TABLE PARENT (
  C1 integer PRIMARY KEY,              --->this is the parent table
  C2 integerPRIMARY KEY,
  c3 integer
);

CREATE TABLE CHILD (                  ---->this is the child table         
  a integer PRIMARY KEY,                 
  b integer,
  c integer,    
  FOREIGN KEY (b, c) REFERENCES PARENT (c1, c2)
);

PRACTICAL 1.CREATION OF SIMPLE POSTGRESQL FOREIGN KEY COLUMN CONSTRAINT:

Let’s say we have a table named products1 AND orders1, products1 is a parent table means referenced table and oreders1 is a child table means referencing table

Parent Table(Referenced Table):
CREATE TABLE products1 (
                       product_no integer PRIMARY KEY,
                       name text,
                       price numeric);

postgres=# \d products1
     Table "public.products1"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 product_no | integer | not null
 name       | text    | 
 price      | numeric | 
Indexes:
    "products1_pkey" PRIMARY KEY, btree (product_no)

Child Table(Foreign key Table or Referencing Table):
CREATE TABLE orders1 (
                      order_id integer PRIMARY KEY,
                      product_no integer REFERENCES products1 (product_no),
                      quantity integer);

postgres=# \d orders1 
      Table "public.orders1"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 order_id   | integer | not null
 product_no | integer | 
 quantity   | integer | 
Indexes:
    "orders1_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
    "orders1_product_no_fkey" FOREIGN KEY (product_no) REFERENCES products1(product_no)

Notice that we use REFERENCES clause to define a foreign key constraint for the orders1 table. It means that product_no  column in the orders1 table references to the product_no column of the products1 table.

PRACTICAL 2.MAKING INSERTION OPERATION ON FOREIGN KEY TABLE:

PostgreSQL Parent(products1) table insertion:

postgres=# insert into products1 values (1,'redmi',1000),(2,'motog4',11000),(3,'samsung edge',18000);
INSERT 0 3

postgres=# insert into products1 values (1,'redmi',1000),(2,'motog4',11000),(3,'samsung edge',18000);
ERROR:  duplicate key value violates unique constraint "products1_pkey"
DETAIL:  Key (product_no)=(1) already exists.

postgres=# insert into products1 values (4,'nokia100',100),(5,'moto apple',110000),(6,'apple8',100000);
INSERT 0 3

postgres=# select * from products1;
 product_no |     name     | price  
------------+--------------+--------
          1 | redmi        |   1000
          2 | motog4       |  11000
          3 | samsung edge |  18000
          4 | nokia100     |    100
          5 | moto apple   | 110000
          6 | apple8       | 100000
(6 rows)

PostgreSQL Child(orders1) table insertion:

postgres=# insert into orders1 values(1,2,10);
INSERT 0 1

postgres=# insert into orders1 values(2,6,10);
INSERT 0 1

postgres=# insert into orders1 values(3,5,10);
INSERT 0 1

postgres=# insert into orders1 values(4,6,80);
INSERT 0 1
postgres=# insert into orders1 values(5,7,80);
ERROR:  insert or update on table "orders1" violates foreign key constraint "orders1_product_no_fkey"
DETAIL:  Key (product_no)=(7) is not present in table "products1".

postgres=# select * from orders1;
 order_id | product_no | quantity 
----------+------------+----------
        1 |          2 |       10
        2 |          6 |       10
        3 |          5 |       10
        4 |          6 |       80
(4 rows)

Note the above error why it throw error means product_no column in products1 table available values are 1,2,3,4,5,6. so this valuse only allowed on product_no column in orders1 table, if you try to insert or upd unrelated(unavailable value product_no column in products1 table) number(like 7...) on product_no column in orders1 table surely you will recieve error.