Foreign Key (Self Reference) Constraint
Foreign Keys represent relationships between tables. A foreign key is a column (or a group of columns) whose values are derived from the primary key or unique key of some other table.
The table in which the foreign key is defined is called a foreign table and such table in which primary key defined and referenced by the foreign key is called the primary table(master table).
Foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using in query as References TableName.ColumnName.
Features of Foreign Keys
1) Foreign key is a column(s) that references a column(s) of table and it can be the same table also
2) Parent that is being referenced has to be unique or primary key
3) Child may have duplicates and nulls but unless it is specified
4) Foreign key constraint can be specified on child but not on parent
5) Parent record can be delete provided no child record exist.
6) Master table(primary table) can not be updated if child record exist.
This constraint establishes a relationship between records (column data) across a primary table (master table) and foreign table. This relationship ensures:
1) Records can not be inserted into a foreign table if corresponding records in the master table do not exist.
2) Records of the master table can not be deleted if corresponding records in the foreign table actually exist.