FOREIGN KEY

FOREIGN KEY is a column or multiple columns, which serve to indicate which is the primary key of another table.

The column or columns listed as FOREIGN KEY may only have values that already exist in the primary key of another table.

Example FOREIGN KEY

table "departments" with primary key "dep"

depdepartment
1ADMINISTRATION
2COMPUTER
3BUSINESS

table 'people' with a FOREIGN KEY 'dep', which refers to the primary key 'dep' of the previous table 'departments' and therefore can only have a value which is in the table

<
pernamesurnamedep
1HARRYCLARK1
2HARRYSMITH2
3JOHNBROWN3

FOREIGN KEY definitions in CREATE TABLE for MySQL

CREATE TABLE departments { dep int NOT NULL, department varchar (255), PRIMARY KEY (dep) }

CREATE TABLE people { per int NOT NULL, name varchar (255), surname varchar (255), dep int NOT NULL, PRIMARY KEY (per), FOREIGN KEY (dep) REFERENCES departments (dep) }

FOREIGN KEY definitions in CREATE TABLE to ORACLE, ACCESS, SQLSERVER

CREATE TABLE departments { dep int NOT NULL PRIMARY KEY, department varchar (255), }
CREATE TABLE people { per int NOT NULL PRIMARY KEY, name varchar (255), surname varchar (255), dep int FOREIGN KEY REFERENCES departments (dep) }

If FOREIGN KEY is composed of several columns and want to put a name :

CONSTRAINT fkpeople FOREIGN KEY (dep, id) REFERENCES departments(dep,id)

Example with ALTER TABLE FOREIGN KEY

ALTER TABLE ADD FOREIGN KEY (dep) REFERENCES departments(dep)

Example multiple FOREIGN KEY (multiple columns) with ALTER TABLE:

ALTER TABLE ADD CONSTRAINT fkpeople FOREIGN KEY (dep) REFERENCES departments(dep)

To delete a FOREIGN KEY use DROP, but varies according to the data base:

Remove FOREIGN KEY in MySQL

ALTER TABLE people DROP FOREIGN KEY dep

Remove FOREIGN KEY in ORACLE, SQLSERVER and ACCESS

ALTER TABLE people DROP CONSTRAINT dep