![]() The above example shows the orders table contain FOREIGN KEY constraint which referencing form two tables. Pictorial representation of more than one PostgreSQL FOREIGN KEY constraint Explain ![]() SQL CREATE TABLE orders(ord_no integer,ord_date date,item_code integer REFERENCES items,vendor_code integer REFERENCES vendors,item_grade character(1),ord_qty numeric,ord_amount numeric,PRIMARY KEY (item_code,vendor_code)) Let us assume that, we want to take order those items and vendor in orders table thus that the items must be appearing in items table and vendors must appearing in the vendors table, the following SQL can be executed. Table contain more than one PostgreSQL FOREIGN KEY constraintĪssume that the structure of items table is - CREATE TABLE items(item_code integer PRIMARY KEY,item_name character(35),purchase_unit character(10),sale_unit character(10),purchase_price numeric(10,2),sale_price numeric(10,2)) The point to be remembered that, type and number of columns in FOREIGN KEY must be matched with the type and number of columns with the referenced columns in referenced table. The PRIMARY KEY constraint contain one column and the FOREIGN KEY constrain contain a group of columns. The above example shows the orders table created with two constraints one PRIMARY KEY and one FOREIGN KEY. Pictorial representation of FOREIGN KEY constraint on group of columns Explain SQL CREATE TABLE orders(ord_no integer PRIMARY KEY,ord_date date,item_code integer ,item_name character(35),item_grade character(1),ord_qty numeric,ord_amount numeric,FOREIGN KEY (item_code,item_name) REFERENCES items(item_code,item_name)) Let us assume that, we want to take order with a unique order_no and those items with their code in orders table which must be appearing in items table, the following SQL can be executed. PostgreSQL FOREIGN KEY constraint on group of columns OR the above can be written as bellow - CREATE TABLE orders(ord_no integer PRIMARY KEY,ord_date date,item_code integer REFERENCES items,item_grade character(1),ord_qty numeric,ord_amount numeric) īecause if mention no column(s) as reference the primary key(s) of referenced table is used as referenced column(s). Here orders table is the referencing table and items is the referenced table and the item_code or orders table is the referencing column and the item_code of items table is the referenced column. The above example and picture explain that the item_code column of orders table can only contain the values which appearing in the item_code column of items table. Pictorial representation of FOREIGN KEY constraint Explanation SQL CREATE TABLE orders(ord_no integer PRIMARY KEY,ord_date date,item_code integer REFERENCES items(item_code),item_grade character(1),ord_qty numeric,ord_amount numeric) Let us assume that we want to take order with a unique order_no and those items in orders table which must be appearing in items table, the following SQL can be executed. The table structure SQL CREATE TABLE items(item_code integer,item_name character(35),purchase_unit character(10),sale_unit character(10),purchase_price numeric(10,2),sale_price numeric(10,2),PRIMARY KEY (item_code,item_name)) SQL CREATE TABLE vendors(vendor_code integer PRIMARY KEY,vendor_name character(35),vendor_city character(15),vendor_country character(20)) The vendors table contain a primary key vendor_code and the items table contain the primary key item_code. Let us consider two tables vendors and items to illustrate the FOREIGN KEY in PostgreSQL. A foreign key constraint, also known as Referential integrity Constraint, specifies that the values of the foreign key correspond to actual values of the primary key in the other table. The PostgreSQL FOREIGN KEY is a combination of columns with values based on the primary key values from another table. ![]() A FOREIGN KEY constraint contains the value in a column or combination of columns which must be appearing in the same column or group of columns in another table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |