Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Foreign Key -1

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.

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL