|
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.
|
