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.
PRACTICAL 1.CREATION OF SIMPLE POSTGRESQL FOREIGN KEY COLUMN CONSTRAINT:
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
Post a Comment