PostgreSQL Foreign Key -2

PRACTICAL 3.ANOTHER WAY TO DEFINE A FOREIGN KEY CONSTRAINT IS TO USE THE TABLE CONSTRAINT AS FOLLOWS:

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,
                      quantity integer,
                       foreign key(product_no REFERENCES products1 (product_no));

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)

• I didn’t specify a name for the foreign key constraint explicitly, PostgreSQL assigned a name with the pattern: tablename_columnname_fkey.
• In our above example, PostgreSQL creates a foreign key constraint as "orders1_product_no_fkey"

PRACTICAL 4.FOREIGN KEY CONSTRAINT ABSENCE OF REFERNCED COLUMN:
From below example Absence of a column list the primary key of the referenced table is used as the referenced column

Parent table(Referenced table):
postgres=#CREATE TABLE sales (
    id integer PRIMARY KEY,
    product_name varchar,
    quantity integer
);

postgres=# \d sales
             Table "public.sales"
    Column    |       Type        | Modifiers 
--------------+-------------------+-----------
 id           | integer           | not null
 product_name | character varying | 
 quantity     | integer           | 
Indexes:
    "sales_pkey" PRIMARY KEY, btree (id)


Child table(Referencing table):
postgres=# CREATE TABLE car (
    car_no integer PRIMARY KEY,
    car_name varchar,
    sales_id integer REFERENCES sales
);

postgres=# \d car
            Table "public.car"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 car_no   | integer           | not null
 car_name | character varying | 
 sales_id | integer           | 
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)
Foreign-key constraints:
    "car_sales_id_fkey" FOREIGN KEY (sales_id) REFERENCES sales(id)

PRACTICAL 5.FOREIGN KEY CONSTRAINT ON GROUP OF COLUMN:

• A foreign key can also constrain and reference a group of columns. As usual, it then needs to be written in table constraint form.
• the number and chareacter of the constrained columns need to match the number and character of the referenced columns.

Parent table(Referenced table):
postgres=# CREATE TABLE sales1 (
    id integer PRIMARY KEY,
    reg_id integer,
    product_name varchar,
    quantity integer
    );

postgres=# \d sales1
            Table "public.sales1"
    Column    |       Type        | Modifiers 
--------------+-------------------+-----------
 id           | integer           | not null
 reg_id       | integer           | 
 product_name | character varying | 
 quantity     | integer           | 
Indexes:
    "sales1_pkey" PRIMARY KEY, btree (id)



Child table(Referencing table):
postgres=# CREATE TABLE bus1 (
    bus_no integer,
    bus_name varchar,
    sales_id integer, 
   FOREIGN KEY (sales_id,bus_name) REFERENCES sales1(id,product_name)
);

ERROR:  there is no unique constraint matching given keys for referenced table "sales1"

Solution:

first i created single column reference & foreign key table after that i used ALTER TABLE method,i explained following example.

postgres=# CREATE TABLE bus1 (
    bus_no integer,
    bus_name varchar,
    sales_id integer, 
   FOREIGN KEY (sales_id) REFERENCES sales1(id)
);

postgres=# \d bus1
           Table "public.bus1"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 bus_no   | integer           | 
 bus_name | character varying | 
 sales_id | integer           | 
Foreign-key constraints:

postgres=# ALTER TABLE bus1 ADD CONSTRAINT distfk FOREIGN KEY (bus_name) REFERENCES sales1 (product_name);
ALTER TABLE

postgres=# \d bus1
           Table "public.bus1"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 bus_no   | integer           | 
 bus_name | character varying | 
 sales_id | integer           | 
Foreign-key constraints:
    "bus1_sales_id_fkey" FOREIGN KEY (sales_id) REFERENCES sales1(id)
    "distfk" FOREIGN KEY (bus_name) REFERENCES sales1(product_name)

then we will make some insertion on parent and child table

Inserting parent table:
postgres=# insert into sales1 values(1,25363,'benz bus',3),(2,8585,'maruthis',5);
INSERT 0 2
postgres=# insert into sales1 values(3,2843,'honda',7),(4,9085,'yamaha',3);      
INSERT 0 2

postgres=# select * from sales1;
 id | reg_id | product_name | quantity 
----+--------+--------------+----------
  1 |  25363 | benz bus     |        3
  2 |   8585 | maruthis     |        5
  3 |   2843 | honda        |        7
  4 |   9085 | yamaha       |        3
(4 rows)

Inserting child table:
postgres=# insert into bus1 values(97,'honda',1),(56,'yamah',2);           
ERROR:  insert or update on table "bus1" violates foreign key constraint "distfk"
DETAIL:  Key (bus_name)=(yamah) is not present in table "sales1".

solution:
"yamah" is not in sales1(parent) table only "yamaha" is there so change the "yamaha"
postgres=# insert into bus1 values(97,'honda',1),(56,'yamaha',2);
INSERT 0 2

postgres=# select * from bus1;
 bus_no | bus_name | sales_id 
--------+----------+----------
     97 | honda    |        1
     56 | yamaha   |        2
(2 rows)