SQL FOREIGN KEY Constraint

SQL Foreign key constraint provides a parent-child relationship between two columns of different tables or same table. The child column can only have values which are already defined in parent column; no other values can be entered except NULL values. The column must contain Primary Key constraint to be used as parent column.  SQL foreign key is then defined on child column.SQL Foreign key columns refer to primary key columns in same table or another table.  The foreign key can also contain nulls.

ADDING A FOREIGN KEY
SQL Foreign key can be added using

1. Create Statement
2. Alter Statement

Adding SQL Foreign Key using CREATE:

Example:
Column mgr_id denotes manager in department table, so it should must belong to some employee in employee table. To maintain this condition, you can define foreign key on mgr_id. The following query will define SQL foreign key on column mgr_id of department table using CREATE statement:

CREATE TABLE department (dept_no NUMBER(2),  dept_name VARCHAR2(15), mgr_id NUMBER,  FOREIGN KEY (mgr_id) REFERENCES employee(emp_id),  mgr_start_date DATE);

Example 2:
The following query is same to one above except that it defines SQL foreign key constraint with name fk_mgr_id

CREATE TABLE department (dept_no NUMBER(2), dept_name VARCHAR2(15), mgr_id NUMBER, CONSTRAINT fk_mgr_id FOREIGN KEY (mgr_id) REFERENCES employee(emp_id),  mgr_start_date DATE);

Adding Foreign Key using ALTER:

Example:
The following query will define SQL foreign key on mgr_id column of department given that there is no SQL foreign key existed earlier:

ALTER TABLE department ADD FOREIGN KEY (mgr_id) REFERENCES employee(emp_id);

Example 2:
The following query is same to one above except that it defines SQL foreign key constraint with name fk_mgr_id

ALTER TABLE department ADD CONSTRAINT fk_mgr_id FOREIGN KEY (mgr_id) REFERENCES employee(emp_id);

DROPPING A FOREIGN KEY
SQL Foreign key constraints can only be dropped using constraint name. So in case you have not defined the name for SQL foreign key constraint, the system will itself define a name for constraint which can be obtained using following SQL:

SELECT  constraint_name FROM user_constraints WHERE constraint_type = ‘R’ AND UPPER(table_name) LIKE  'TABLE_NAME_IN_CAPITAL'

Example:
The following statement will get constraint name for SQL foreign key which will then be used to drop foreign key from department table:

SELECT  constraint_name FROM user_constraints WHERE constraint_type = ‘R’ AND UPPER(table_name) LIKE  'DEPARTMENT'

Result:
SYS_C003851503

This statement will drop SQL foreign key using its name retrieved in previous query:

ALTER TABLE department DROP CONSTRAINT SYS_C003851503;

Foreign keys ensure data integrity in a table by maintaining referential integrity in each record of the foreign key column. This means columns in a SQL foreign key cannot contain values other that primary key column referenced as parent column.