Here is how a foreign key works (so that orphan rows can not happen):
When inserting a new row in the foreign key table:
The DBMS system makes sure that the new FOREIGN KEY value matches a UNIQUE or PRIMARY KEY value in the parent table. If there is no match, the new row in the foreign key table will not be inserted.

When updating an existing row in the foreign key table:
The DBMS system makes sure that the updated FOREIGN KEY value matches a UNIQUE or PRIMARY KEY value in the parent table. If there is no match, the existing row in the foreign key table will not be updated.

When deleting an existing row in the foreign key table:
There is no need for a referential integrity check.
When inserting a new row in the parent table:
There is no need for a referential integrity check.

When updating an existing row in the parent table:
The DBMS system makes sure that none of the FOREIGN KEY values match a UNIQUE or PRIMARY KEY value in the child table. If a match exists, the existing row in the parent key table will not be updated. However, if ON UPDATE CASCADE is specified, the parent row and all matching foreign key rows will be updated with the new key values.

When deleting an existing row in the parent table:
The DBMS system makes sure that none of the FOREIGN KEY values match a UNIQUE or PRIMARY KEY value in the child table. If a match exists, the existing row in the parent key table will not be deleted. However, if ON UPDATE CASCADE is specified, the parent row and all matching foreign key rows will be deleted.

A foreign key has the following features:> A table can have zero, one, or many foreign keys. It uses the reserved words REFERENCE or FOREIGN KEY in the CREATE TABLE statement.

> A simple key (one column) can be defined as a column constraint or a table constraint. As a simple key column constraint, REFERENCES is placed in the column definition.

> A composite key (multiple columns) can only be defined as a table constraint. As a table constraint, the REFERENCES and PRIMARY KEY definitions have the column names specified.

> A FOREIGN KEY column can reference a PRIMARY KEY or a UNIQUE column in another table, and this references column is called the parent key. The FOREIGN KEY and parent key columns must be of the same or convertibly similar data type.

> Foreign key constraints make updates and deletes easier, because inconsistencies are not introduced into the database. For example, you can not place an employee into the payroll table, until that employee exists in the employee table, and when you delete the employee from the employee table, the employee will also be deleted from the payroll table. This prevents orphan rows.

> The general practice is to use a CONSTRAINT clause to explicitly name all foreign keys. This gives a foreign key constraint name to make maintenance easier.

Foreign keys were discussed in Relational Database Basics. Let us review:Foreign keys are used to associate data between tables. For instance, a foreign key of REP in the CUSTOMER_TBL points to the primary key EMPL_ID in the EMPLOYEE_TBL.

> A foreign key is one or more columns in a table whose values reference values in another table.

> A foreign key in the child table establishes a relationship to the primary key in the parent table.

> The matching foreign key and primary key can have different names.

> If the relationship is enforced, then this is called referential integrity.

> If the relationship is not enforced and there is a foreign key in a child table that does not match the primary key of the parent table, then this is called an orphan row.

> Unlike primary keys, foreign keys can be empty with a null.

> Foreign keys are not always unique in their table.

> A foreign key can reference the primary key of the same table. For instance, the MANAGER of an employee can reference the EMPLOYEE ID for the manager.