Q

Integrity constraints and referential integrity

What is the difference between integrity constraints and referential integrity?

This Content Component encountered an error

What is the difference between integrity constraints and referential integrity?

Good question. Referential integrity, also known as relational integrity, means that if a table contains a foreign key column, then every value in that column (except NULL, if it is allowed) will be found in the primary key of the table that it is related to, or references (the syntax for declaring the foreign key uses the keyword REFERENCES).

For example, let's say you have a table of users and a table of images, where each image belongs to a user:

users
id  name
23  Tom
25  Dick
31  Harry

   
images
id   user   path
187   23    /images/emo.jpg
188   31    /images/kitten.jpg
189   24    /images/manamana.jpg
190   25    /images/phlogiston.jpg

Can you spot the referential integrity problem?

Sometimes it's difficult to convince experienced programmers that they should declare their tables with foreign keys. "Why couldn't we," they ask, "just do a lookup on the user to ensure it exists, and then just insert the image?" The answer to that question, of course, is that (a) not all updates to the database are going to happen through the application (updates can also occur via bulk loads, SQL windows, command lines, etc.), and (b) why do programmers feel they need to reinvent the wheel? The database will look it up for you if the foreign key has been declared.

Referential integrity is only one type of database integrity, although arguably it might be the most important. Integrity constraints are any constraints that ensure integrity in databases. The various types of integrity constraints are:

NOT NULL
ensures that a column cannot have a NULL value.
UNIQUE KEY
ensures that each value in the key occurs at most once (note that unique keys can consist of more than one column).
PRIMARY KEY
same as unique key, except a primary key cannot be NULL. Primary key integrity is also called Entity Integrity.
FOREIGN KEY
values (except NULL, if allowed) must be found in referenced primary or unique key.
CHECK
provides a condition which is enforced on every row of the table. For example, CHECK (age >= 18) ensures that you cannot insert or update a row with an age less than 18. Check constraints are often used for Domain Integrity, to ensure that a column will have only certain specified values.
ASSERTION
a condition enforced across tables (and therefore declared separately from CREATE TABLE statements). Few DBMS systems support assertions.
This was first published in August 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close