What is Referential Integrity in databases?
By: Peter den Haan
Take the example of the Book database as shown below.
Table |
Column |
Key |
---|---|---|
Book |
ID |
Primary key |
Title |
Not a key field |
|
Price |
Not a key field |
|
Author |
ID |
Primary key |
Author_Name |
Not a key field |
|
Contribution |
ID |
Primary key |
Title_ID |
Foreign key |
|
Author_ID |
Foreign key |
What happens when you start manipulating the records in your tables? You can edit the book information at will without any ill effects, but what would happen if you needed to delete a title? The entries in the Contribution table will still link to a nonexistent book. Clearly you can't have a contribution detail without the associated book title being present. So, you must have a means in place to enforce a corresponding book title for each contribution. This is the basis of enforcing referential integrity. You can enforce the validity of the data in this situation in two ways. One is by cascading deletions through the related tables; the other is by preventing deletions when related records exist.
Note |
Referential integrity prevents inconsistent data from being created in the database by ensuring that any data shared between tables remains consistent. To put it another way, it ensures that the soundness of the relationships remains intact. |
Database applications have several choices available for enforcing referential integrity, but if possible, you should let the database engine do its job and handle this for you. Database engines allow you to use declarative referential integrity. You specify a relationship between tables at design time, indicating if updates and deletes will cascade through related tables. If cascading updates are enabled, changes to the primary key in a table are propagated through related tables. If cascading deletes are enabled, deletions from a table are propagated through related tables.
Before you go ahead and enable cascading deletes on all your relationships, keep in mind that this can be a dangerous practice. If you define a relationship between the Author table and the Title table with cascading deletes enabled and then delete a record from Author, you'll delete all Title table records that come under this category. Be cautious, or you may accidentally lose important data.
Archived Comments
1. i found this very pleasing for both my brain and my peen.
View Tutorial By: Matthew Redisons at 2012-10-03 14:36:32
2. Nice explanation. :) too easy to understand
View Tutorial By: Sheba at 2012-02-16 05:11:18
3. Nice explanation. :) too easy to understand
View Tutorial By: Sheba at 2012-02-16 05:10:26
4. Awsome...Example...
View Tutorial By: sitakanta at 2011-12-05 12:22:03
5. nicely explained tyvm a much appreciated post
View Tutorial By: dmomo at 2011-10-18 23:27:24
6. hey thats a superb way of explaining in plain terms ....very awsome job
View Tutorial By: Agastya at 2011-05-08 03:11:53
7. Good
View Tutorial By: sirisha at 2009-09-08 23:04:22
8. really superb in simple english with examples. Expecting more tutorials on web service , ruby on rai
View Tutorial By: hariharan at 2009-01-14 22:22:32
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Calling a Stored Procedure from JDBC in Java
setSavepoint and releaseSavepoint Example in Java
PreparedStatement Example in Java
Creating Database Tables Using ANT
Using the DriverManager Class vs Using a DataSource Object for a connection
Stored Procedures example in SQL