Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?