Foreign keys in a database :
A foreign key in a My Sql database is a field (or fields) of a table that points to a primary key of an other table. Such keys are defined in a database to ensure that only fields that supposed to appear in a database may be permitted.
What is the need of a foreign key :
For a long time MySQL do not support foreign keys being afraid of the fact that it would erode the speed and performance of the RDBMS. But considering the fact that foreign key relationships can play an important role in catching data entry errors and may result in a stronger and better-integrated database, MySQL included the foreign keys.
In order to set up a foreign key relationship between two MySQL tables, three conditions must be met:
- Both tables must be of the InnoDB table type.
- The fields used in the foreign key relationship must be indexed.
- The fields used in the foreign key relationship must be similar in data type.
Following examples may help you understand the need and use of foreign keys.
EXAMPLE :
We have two tables, say Students and Marks.
Students Table :-
Roll No. Name Surname
001 Sulabh Jain
002 Pratik Verma
003 Kuldeep Kumar
004 Anupam Jain
Marks Table :-
S No Name Roll No Subject Marks
1 Sulabh 001 Maths 87
2 Sulabh 001 English 76
3 Kuldeep 003 English 81
4 Pratik 002 Chemistry 72
In the Marks table, the field Roll No is a foreign key referenced to the Roll No field of Students table.
Creating a table with a foreign key :
CREATE TABLE `marks`
( `S No` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(45) NOT NULL,
`Roll No` int(10) unsigned NOT NULL,
`Subject` varchar(45) NOT NULL,
`Marks` int(10) unsigned NOT NULL,
PRIMARY KEY (`S No`), KEY `Roll No` (`Roll No`),
CONSTRAINT `Roll No` FOREIGN KEY (`Roll No`)
REFERENCES `Students` (`Roll No`) )
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Foreign Key Constraints :
Try the following query,
INSERT INTO TABLE Marks (S No, Name, Roll No, Subject, Marks) values (5, ‘Anupam’, 005, ‘Quantum’, 81);
(Notice that there is no value like 005 in the Roll No column of Students table to which the Roll No column of Marks table is referenced)
If we try to enter a value for the field Roll No in II table (Marks) which do not exists in the Roll No column of I table (Students), in such a case we will get an error like,
Cannot add or update a child row: a foreign key constraint fails
How to drop a FOREIGN KEY :
We use the ALTER TABLE command with the DROP FOREIGN KEY clause, for dropping a foreign key,
ALTER TABLE Marks DROP FOREIGN KEY Roll No ;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
Whether any key is a foreign key or not, could be easily checked by using the SHOW CREATE TABLE query.
References :
1. SQL Foreign Keys, http://ezdia.com/SQL_Foreign_Key/Content.do?id=1294
2. Referential Integrity, http://ezdia.com/Referential_Integrity_in_databases/Content.do?id=1295
