lishman levelup
«previous  next»


SQL Basics
Joins
Constraints




Oracle Integrity Constraints


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