codescracker


sql

SQL Foreign Key



« Previous Tutorial Next Tutorial »


SQL FOREIGN KEY constraint in one table points to a PRIMARY KEY in another table.

Let us demonstrate the SQL foreign key with an example. Just look at the following two tables (named Students and Teachers):

sql foreign key

sql foreign key constraint

Note that the column "P_ID" in the table "Teachers" points to the column "P_ID" in the table "Students".

The column "P_ID" in the table "Students" is the PRIMARY KEY in table "Students". And the column "P_ID" in the table "Teachers" is a FOREIGN KEY in the table "Teachers".

The FOREIGN KEY constraint in SQL, is used to prevent the actions that would destroy the links between the tables. The SQL FOREIGN KEY constraint also prevents the 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.

SQL FOREIGN KEY Constraint on CREATE TABLE

Here are the SQL statements creates FOREIGN KEY on the column "P_ID" when the table "Teachers" is created:

For MySQL Database

CREATE TABLE Teachers
(
U_ID int NOT NULL,
Name char(50),
P_ID int,
Address char(100),
PRIMARY KEY (U_ID),
FOREIGN KEY (P_ID) REFERENCES Students(P_ID)
)

For SQL Server / Oracle / MS Access Databases

CREATE TABLE Teachers
(
U_ID int NOT NULL PRIMARY KEY,
Name char(50),
P_ID int FOREIGN KEY REFERENCES Students(P_ID)
Address char(100)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

Here are the SQL statements used to create a FOREIGN KEY constraint on the column "P_ID" when the table "Teachers" is already created:

For MySQL / SQL Server / Oracle / MS Access Databases

ALTER TABLE Teachers
ADD FOREIGN KEY (P_ID)
REFERENCES Students(P_ID)

DROP SQL FOREIGN KEY Constraint

Here are the SQL statements used to drop a FOREIGN KEY constraint:

For MySQL Database

ALTER TABLE Teachers
DROP FOREIGN KEY fk_PerTeachers

For SQL Server / Oracle / MS Access Databases

ALTER TABLE Teachers
DROP CONSTRAINT fk_PerTeachers

« Previous Tutorial Next Tutorial »



Tools
Calculator

Quick Links
Signup - Login - Give Online Test