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.

Constraint types

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").

alter table ri_not_null modify a null;

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);

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);

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
);

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 Constraints

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;

Data dictionary

Oracle stores the definitions of integrity constraints in the data dictionary.

Thanks

Thanks to Kieron Bird who notified me of an error on this page.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback