The FOREIGN KEY constraint is used to point to primary key of another database table. Foreigh key ensures that the data exists when we have reference to it.
For example, we have a table of Employee (EmployeeId is Primary):
EmployeeId | FirstName | LastName |
---|---|---|
1 | Mkai | Khaao |
2 | Hiaf | Lihaaa |
And we have another table Project:
ProjectId | ProjectName | ProjectManagerId |
---|---|---|
1 | Basic Project | 1 |
2 | Premium Project | 2 |
The ProjectManagerId in table Project points to EmployeeId in table Employee. That means only an exist employee can be as a project manager. If you try to set ProjectManagerId=3, it will fail, because it doesn’t have an employee with EmployeeId=3.
To create a table with FOREIGN KEY:
SQL Server / Oracle / MS Access
CREATE TABLE Project ( ProjectID int NOT NULL PRIMARY KEY, ProjectName varchar(100) NOT NULL, ProjectManagerId int FOREIGN KEY REFERENCES Employee(EmployeeId) )
My SQL
CREATE TABLE Project ( ProjectId int NOT NULL, ProjectName varchar(100) NOT NULL, ProjectManagerId int, PRIMARY KEY (ProjectId ), FOREIGN KEY (ProjectManagerId) REFERENCES Employee(EmployeeId) )
If you want to name a foreign key constraint:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Project ( ProjectId int NOT NULL, ProjectName varchar(100) NOT NULL, ProjectManagerId int, PRIMARY KEY (ProjectId ), CONSTRAINT fk_EmployeeId FOREIGN KEY (ProjectManagerId ) REFERENCES Employee(EmployeeId) )
To set FOREIGN KEY by using ALTER TABLE:
SQL Server / MySQL / Oracle / MS Access:
ALTER TABLE Project ADD FOREIGN KEY (ProjectManagerId ) REFERENCES Employee(EmployeeId)
To drop a PRIMARY KEY:
SQL Server / Oracle / MS Access:
ALTER TABLE Project DROP CONSTRAINT fk_EmployeeId
My SQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_EmployeeId