Previous | Home | Next |
FOREIGN KEY constraint to remove the inconsistency in two tables when data in one table depends on data in another table.
A FOREIGN KEY constraint associates one or more columns of a table (the foreign key) with an identical set of columns on which a PRIMARY KEY constraint has been defined (a primary key column in another table).
CREATE TABLE table_name (column_name data_type REFERENCES table_name (emp_id))
Syntax:
CONSTRAINT constraint_name FOREIGN KEY (column_name,..) REFERENCE table_name (column_name,..)
Example:
CREATE TABLE employee (emp_name char(4), emp_id varchar (16) REFERENCES engineer (emp_id))
The above command creates a FOREIGN KEY on the attribute emp_id of the employee table that reference the primary key emp_id of the engineer table. This will ensure that the employeeID code that is inserted into the employee table is checked against the engineer table for the validity.
output
![](Image/foreign_key.gif)
If the employee table is exist and does not have foreign key defined, then table also modified using ALTER TABLE command.
ALTER TABLE employee ADD CONSTRAINT fkemp_id FOREIGN KEY (emp_id) REFERENCES engineer (emp_id)
output
![](Image/for_alter.gif)
Previous | Home | Next |