How Referential Integrity Works with an Oracle Database
Referential integrity is about ensuring that relationships between rows in related tables are valid and that you do not accidentally delete or change related data. When referential integrity is enforced in an Oracle database, you must observe the following rules:
- You cannot enter a value in the foreign key column of the related table if that value does not exist in the primary key of the related table. However, you can enter a null in the foreign key column. For example, you cannot indicate that a job is assigned to an employee who is not included in the
EMPLOYEEtable, but you can indicate that an employee has no assigned job by entering a null in the
JOB_IDcolumn of the
- You cannot delete a row from a primary key table if rows matching it exist in a related table. For example, you cannot delete a row from the
JOBStable if there are employees assigned to the job represented by that row in the
EMPLOYEEtable. However, if cascading deletes are enabled, you can delete a primary key row; all matching rows in related tables are also deleted.
- You cannot change a primary key value in the primary key table if that row has related rows. For example, you cannot delete an employee from the
EMPLOYEEtable if that employee is assigned to a job in the
Oracle supports only two kinds of referential integrity:
- Enabling referential integrity to check values in related tables when you enter data. If the data value is not allowed (as determined by the rules above), the data entry fails and the data is not added to the database.
- Cascading deletes.
SQL Server, on the other hand, supports some additional options for referential integrity. With SQL Server, you can set options to disable the foreign key constraint under these conditions:
- Upon creation, to check existing data.
- During INSERT and UPDATE transactions.
- During replication.
As a result, the following topics in the Database Designer documentation are not valid for Oracle databases:
- Checking Existing Data when Creating a Relationship
- Disabling a Foreign Key Constraint with INSERT and UPDATE Statements
- Disabling a Foreign Key Constraint for Replication