Interview Questions   Tutorials   Discussions   Programs   

DB2 - What is the Foreign key? explain?




368
views
asked mar August 25, 2014 11:38 AM  

What is the Foreign key? explain?


           

1 Answers



 
answered By Mswami   0  
A foreign key is a key used to link two tables together. This is sometimes called a referencing key.

Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

A foreign key is a column (or columns) that references a column (most often the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Example1:

Consider the structure of the two tables as follows:

CUSTOMERS table:

CREATE TABLE CUSTOMERS(
    ID  INT       NOT NULL,
    NAME VARCHAR (20)   NOT NULL,
    AGE INT       NOT NULL,
    ADDRESS CHAR (25) ,
    SALARY  DECIMAL (18, 2),   
    PRIMARY KEY (ID)
);

ORDERS table:

CREATE TABLE ORDERS (
    ID     INT    NOT NULL,
    DATE    DATETIME,
    CUSTOMER_ID INT references CUSTOMERS(ID),
    AMOUNT   double,
    PRIMARY KEY (ID)
);


If ORDERS table has already been created, and the foreign key has not yet been set, use the syntax for specifying a foreign key by altering a table.

ALTER TABLE ORDERS
  ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);


Example2:

CREATE TABLE album(
 id CHAR(10) NOT NULL PRIMARY KEY,
 title VARCHAR(100),
 artist VARCHAR(100)
);

CREATE TABLE track(
 album CHAR(10),
 dsk INTEGER,
 posn INTEGER,
 song VARCHAR(255),
 FOREIGN KEY (album) REFERENCES album(id)
);
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert