Tables > Table Relations > Referential Integrity KEYVIOL

Key Violations from creating Referential Integrity

When creating Referential Integrity between a Parent table and a selected Child table, records in the Child table where the linked field values do not match the linked field values of the Parent table will be considered as Child table orphaned records.

When creating a Referential Integrity relation, these orphaned records will be deleted form the Child relation table and saved in a key violation table named KEYVIOL.db or KEYVIOLn.db (KEYVIOL1.db, KEYVIOL2.db, etc.) structured equivalent to the Child relation table.

So, when creating a Referential Integrity between two tables, do look for possible creation of KEYVIOL tables of the same date and time as of the Child table.

Orphaned records

If you want to prepare against deletion of such orphaned records, you may want to identify such Child table records before creating the Referential Integrity relation, by executing an SQL Select like this:

SELECT
c.LinkField AS ChildField
, p.LinkField AS ParentField
FROM ChildTable c
LEFT JOIN ParantTable p
ON c.LinkField = p.LinkField
WHERE p.LinkField IS NULL;

or for the DBDemos Employee to Orders relation:

SELECT
o.EmpNo AS OrdesEmpNo
, e.EmpNo AS EmployeeEmpNo
FROM Orders o
LEFT JOIN Employee e
ON o.EmpNo = e.EmpNo
WHERE e.EmpNo IS NULL;