96.19%
Search · Index

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.