We made some fundamental assumptions in the previous sections.
In particular, we took it for granted that results from the queries were correct because
the data in the tables was correct. However, there is nothing stopping us from making changes
which would invalidate this data. Here are a few examples.
Missing Values
A value, which is essential to an application, is set to null.
INSERT INTO continent (cont_id, cont_name) VALUES (1, '');
UPDATE continent
SET cont_id = null
WHERE cont_name = 'Asia';
An empty string ('') is equivalent to null in Oracle.
Duplicate Values
Duplicate values exist in a column which should only contain distinct values.
INSERT INTO continent (cont_id, cont_name) VALUES (3, 'Europe');
INSERT INTO continent (cont_id, cont_name) VALUES (4, 'Europe');
UPDATE continent
SET cont_id = 3
WHERE cont_name = 'Antarctica';
Invalid References
A row in one table holds a reference to a row in another table that no longer exists.
DELETE FROM continent
WHERE cont_id = 6;
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(53, 6, 'Australia', 2966200, 21884000, '4-sep-09', 'Australian Dollar');
All this means that the CONTINENT table is a mess:
SELECT *
FROM continent;
| CONT_ID |
CONT_NAME |
| 1 |
(null) |
| (null) |
Asia |
| 3 |
Europe |
| 4 |
Europe |
| 5 |
South America |
| 3 |
Antarctica |
Strange things are sure to happen when we query this data, especially
when using joins.
Which continent is New Zealand in?
SELECT cont_name
FROM continent
JOIN country USING (cont_id)
WHERE ctry_name = 'New Zealand';
-- 0 rows selected
Which continent is Greece in?
SELECT cont_name
FROM continent
JOIN country USING (cont_id)
WHERE ctry_name = 'Greece';
| CONT_NAME |
| Europe |
| Antarctica |
Solution
We need the database to guarantee that the data contained within it, is both accurate and consistent.
These five integrity constraints do just that.
NOT NULL- Unique
- Primary Key
- Foreign Key
- Check Constraint
Let's look at each of these in turn.
|
