FOREIGN keys in MySQL:

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let say we have two tables here, Emp & Dept. Note that DeptNo column in Dept table points to column "DeptNo" in EMP table.

 

 

 

 

 

 

 

Following observations may be made:

• "DeptNo" column is a PRIMARY KEY in Dept Table.
• "DeptNo" column is a FORIEGN KEY in Emp Table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents that invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to, as in the example below:

FOREIGN KEY CONSTRAINT on CREATE TABLE Emp3 (

   EmpNo NUMBER(4) NOT NULL PRIMARY KEY,

   DeptNo NUMBER(7,2) NOT NULL,

   Ename VARCHAR2(9) NOT NULL,

   CONSTRAINT fk_Emp_DeptNo FOREIGN KEY (DeptNo) REFERENCES Dept(DeptNo)

   );

A FOREIGN KEY constraint on ALTER TABLE will be done like this:

ALTER TABLE Emp3 ADD FOREIGN KEY (DeptNo) REFERENCES Dept(DeptNo);