This documentation is archived and is not being maintained.

How Referential Integrity Works with an Oracle Database

Visual Studio .NET 2003

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 EMPLOYEE table, but you can indicate that an employee has no assigned job by entering a null in the JOB_ID column of the EMPLOYEE table.
  • 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 JOBS table if there are employees assigned to the job represented by that row in the EMPLOYEE table. 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 EMPLOYEE table if that employee is assigned to a job in the JOBS table.

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:

See Also

Enforcing Referential Integrity Between Oracle Tables | Data Integrity | General Considerations for Using Visual Database Tools with Oracle Databases