|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Integrity Constraints [Oracle]
Data integrity allows to define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn't meet these requirements, Oracle will not allow so.
There are five integrity constraints in Oracle.
A column in a table can be specified not null. It's not possible to insert a null in such a column. The default is null. So, in the following create table statement, a null can be inserted into the column named c.
create table ri_not_null ( a number not null, b number null, c number ); insert into ri_not_null values ( 1, null, null); insert into ri_not_null values ( 2, 3, 4); insert into ri_not_null values (null, 5, 6);
The first two records can be inserted, the third cannot, throwing a ORA-01400: cannot insert NULL into ("RENE"."RI_NOT_NULL"."A").
The not null/null constraint can be altered with
alter table ri_not_null modify a null;
After this modification, the column a can contain null values.
The unique constraint doesn't allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed.
create table ri_unique ( a number unique, b number );
However, if a column is not explicitely defined as not null, nulls can be inserted multiple times:
insert into ri_unique values (4, 5); insert into ri_unique values (2, 1); insert into ri_unique values (9, 8); insert into ri_unique values (6, 9); insert into ri_unique values (null,9); insert into ri_unique values (null,9);
Now: trying to insert the number 2 again into a:
insert into ri_unique values (2,7);
This statement issues a ORA-00001: unique constraint (RENE.SYS_C001463 violated). Every constraint, by the way, has a name. In this case, the name is: RENE.SYS_C001463.
In order to remove that constraint, an alter table ... drop constraint ... is needed:
alter table ri_unique drop constraint sys_c001463;
Of course, it is also possible to add a unique constraint on an existing table:
alter table ri_unique add constraint uq_ri_b unique (b);
A unique constraint can be extended over multiple columns:
create table ri_3 ( a number, b number, c number, unique (a,b) );
It is possible to name the constraint. The following example creates a unique constraint on the columns a and b and names the constraint uq_ri_3.
create table ri_3 ( a number, b number, c number, constraint uq_ri_3 unique (a,b) );
On a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key.
create table ri_primary_key ( a number primary key, b number );
Primary keys can explicitely be named. The following create table statement creates a table with a primary key whose name is pk_name.
create table ri_primary_key_1 ( a number, b number, c number, constraint pk_name primary key (a, b) );
A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table.
If a table has a foreign key that references a table, that referenced table can be dropped with a drop table .. cascade constraints.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.
A check constraint allows to state a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.
The following table allows only numbers that are between 0 and 100 in the column a;
create table ri_check_1 ( a number check (a between 0 and 100), b number );
Check constraints can be added after a table had been created:
alter table ri_check_1 add constraint ch_b check (b > 50);
It is also possible to state a check constraint that check the value of more than one column. The following example makes sure that the value of begin_ is smaller than the value of end_.
create table ri_check_2 ( begin_ number, end_ number, value_ number, check (begin_ < end_) );
Disabling 'anonymous' constraint
create table foo (bar number, baz number, unique (bar, baz));
alter table foo disable unique (bar, baz);
Disabling named constraint
create table foo (bar number, baz number, constraint uq_foo unique (bar, baz));
alter table foo disable constraint uq_foo;
Oracle stores the definitions of integrity constraints in the data dictionary.
Thanks to Kieron Bird who notified me of an error on this page and Jim Davis who spotted a typo.