VI.3.5 Constraints

Constraints

When you're defining a table, you can constrain single rows by adding some magic words after the data type:

• not null; requires a value for this column
• unique; two rows can't have the same value in this column (side effect in Oracle: creates an index)
• primary key; same as unique except that no row can have a null value for this column and other tables can refer to this column
• check; limit the range of values for column, e.g., rating integer check(rating > 0 and rating <= 10)
• references; this column can only contain values present in another table's primary key column, e.g., user_id not null references users in the bboard table forces the user_id column to only point to valid users. An interesting twist is that you don't have to give a data type for user_id; Oracle assigns this column to whatever data type the foreign key has (in this case integer).

Constraints can apply to multiple columns:

create table static_page_authors (
	page_id		integer not null references static_pages,
	user_id		integer not null references users,
	notify_p	char(1) default 't' check (notify_p in ('t','f')),
	unique(page_id,user_id)
);

Oracle will let us keep rows that have the same page_id and rows that have the same user_id but not rows that have the same value in both columns (which would not make sense; a person can't be the author of a document more than once). Suppose that you run a university distinguished lecture series. You want speakers who are professors at other universities or at least PhDs. On the other hand, if someone controls enough money, be it his own or his company's, he's in. Oracle stands ready:

create table distinguished_lecturers (
	lecturer_id		integer primary key,
	name_and_title		varchar(100),
	personal_wealth		number,
	corporate_wealth	number,
	check (instr(upper(name_and_title),'PHD') <> 0 
               or instr(upper(name_and_title),'PROFESSOR') <> 0 
               or (personal_wealth + corporate_wealth) > 1000000000)
);

insert into distinguished_lecturers 
values
(1,'Professor Ellen Egghead',-10000,200000);

1 row created.

insert into distinguished_lecturers 
values
(2,'Bill Gates, innovator',75000000000,18000000000);

1 row created.

insert into distinguished_lecturers 
values
(3,'Joe Average',20000,0);

ORA-02290: check constraint (PHOTONET.SYS_C001819) violated

As desired, Oracle prevented us from inserting some random average loser into the distinguished_lecturers table, but the error message was confusing in that it refers to a constraint given the name of "SYS_C001819" and owned by the PHOTONET user. We can give our constraint a name at definition time:

create table distinguished_lecturers (
	lecturer_id		integer primary key,
	name_and_title		varchar(100),
	personal_wealth		number,
	corporate_wealth	number,
	constraint ensure_truly_distinguished
        check (instr(upper(name_and_title),'PHD') <> 0 
               or instr(upper(name_and_title),'PROFESSOR') <> 0 
               or (personal_wealth + corporate_wealth) > 1000000000)
);

insert into distinguished_lecturers 
values
(3,'Joe Average',20000,0);

ORA-02290: check constraint (PHOTONET.ENSURE_TRULY_DISTINGUISHED) violated

Now the error message is easier to understand by application programmers.