Integrity Constraints
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.
Constraint types
There are five integrity constraints in Oracle.
Not Null
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 to 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.
Unique Key
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)
);
Primary Key
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)
);
Foreign Key
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.
Check
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_)
);