Foreign Key Constraints: Referential IntegrityA database system needs to be able to enforce business rules. Foreign key constraints used for maintaining Referential integrity is one way Oracle provides for maintaining business rules.
Relational systems allow the enforcement of business rules with constraints, and referential integrity rules form the backbone of relational tables.
Many applications do not use foreign key referential integrity and, instead, rely upon application code to enforce business rules.
Referential-integrity constraints are based on the concept of a foreign key. A foreign key is a column or set of columns in one table that refer to the primary key of another table.
The reference from the foreign key to the primary key is the mechanism whereby the relationships between the occurrences of rows in the two tables are enforced.
One of the most powerful constraints is the referential integrity rule that governs the allowable contents of a column, and in the case of a primary key (PK) and unique key (UK) constraint, in conjunction with foreign keys (FK), define the formal relationship between columns and rows in one table to another.
Creating Foreign key constraints:
ALTER TABLE (table_name)
Referential integrity (RI) rules ensure that one-to-many relationships are enforced within the relational schema.
The referential integrity, foreign key constraint, identifies a column of a table or view as the foreign key.
This foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table.
The table containing the foreign key is referred to as the child, and the table containing the referenced key is the parent table.
The foreign key and referenced key can be in the same table (parent and child are the same table); otherwise, the parent and child tables must be in the same database.
Referential integrity sometimes needs to be double coded: once for the database and again within the application.
As an example, consider the following tables used to store data about members borrowing books from a library:
BOOKS (BookNo, Title, Author).
MEMBERS (MemberID, MemberName, MemberType).
LOANS (BookNo, MemberID, LoanDate, ReturnDate).
BookNo is a foreign key in the loans table because it is the primary key of the books table. Loans is the foreign table and books the primary table. MemberType is a foreign key in the members table because it is the primary key of the membertypes table.
The relational referential-integrity constraint specifies that either of the following must be enforced for a foreign key
While foreign key constraints should always be used in lieu of application-only coded integrity checking, it's important to realize that using referential integrity constraints adds I/O overhead to insert and update operations.
The additional I/O is attributable to the fact that the foreign key must be validated with the parent table. This overhead can be minimized by assuring that referenced columns are indexed.
During bulk loads, it can be advantageous to disable integrity constraints prior to the load and re-enable the constraints after the load is completed. Once constraints are re-enabled, any errors can be noted and handled.
One of the interesting features of referential integrity constraint is ENABLE NOVALIDATE, which can quickly create an enforced constraint, even when the constraint is believed to be true.
If a database process verifies that a FOREIGN KEY constraint is true then rather than have the database re-verify this FOREIGN KEY constraint, which would require time and database resources, the database administrator could instead create a FOREIGN KEY constraint using ENABLE NOVALIDATE. At this stage, I recommend you to read more on Primary and Foreign keys.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.