Foreign key and other constraints in the Oracle database.

What are constraints ?

A foreign key is one of the different types of Oracle constraints.
The purpose of a constraint is literally to constrain or restrict the quality of data which you are allowed to insert into a table.

There are 5 kinds of constraints :

Check

Not-Null

Unique

Primary Key

Foreign Key

Check Constraints

A check constraint allows you to define a function which determines whether the data is allowable or not.

Example: Let's add a check constraint to employees.surname (table Employees, column Surname)
which will only allow characters to be entered in upper case:

SQL> alter table employees add constraint ch_employees check (surname = upper(surname));

If you try to add a surname in lower case , you will receive the following error:

SQL>insert into employees (surname) values ('jones')

ORA-02290: check constraint (CH_EMPLOYEES) violated

Now let's add it the proper way:

SQL> insert into employees (surname) values ('JONES');

1 row created.

Not-Null Constraints

This simple constraint determines whether your column is allowed to contain null data.
By default, a column can contain null data, unless you add a not-null constraint.

Example : Let's add a not-null constraint to Employees.Surname

SQL>alter table employees modify (surname not null);

Now try to insert a null value.

SQL> insert into employees (surname) values (null);

ORA-01400: cannot insert NULL into ("EMPLOYEES"."SURNAME")

Unique Constraints

These constraints enforce uniqueness in your column's data : it will stop duplicated data from being inserted.

Example:

SQL> alter table employees add constraint u_employees unique enable validate;

We know there is already an entry named 'JONES' in the table, so let's see what happens when we try to insert it again:

SQL> insert into employees (surname) values ('JONES');

ORA-00001: unique constraint (U_EMPLOYEES) violated

Primary Key Constraints

A primary key constraint is a combination of a unique and a not-null constraint.
It allows you to use a column in your table to uniquely identify each row by your predetermined key.
Creating a primary key automatically creates a unique index of the same name on the table.
This is how Oracle enforces the uniqueness.

Example: Before we can create a primary key, let's drop the previous not-null and unique key constraints.

SQL> alter table employees modify(surname null);

SQL> alter table employees drop constraint u_employees;

SQL> alter table employees add constraint pk_employee primary key (surname);

Either inserting a pre-existing surname or attempting to insert a null value into the surname column
will now result in an error.

SQL> insert into employees (surname) values ('JONES');

ORA-00001: unique constraint (PK_EMPLOYEES) violated

SQL> insert into employees (surname) values (null);

ORA-01400: cannot insert NULL into ("EMPLOYEES"."SURNAME")

Foreign Key Constraints

Another name for these is relational integrity constraints.
They enforce logical relationships between parent and child tables.

Let's use a geographical example.

We have a parent table called Countries and a child table called Cities.
A row in the Cities table must be able to find its parent Country in the Countries table
before you are allowed to insert the City.

Parent table Countries :

CountryID	CountryName
1	UK
2	USA
3	INDIA

Child table Cities:

CityID	CityName	CountryID
M00001	MADRAS	3
F00001	FARMINGTON	2
B00001	BOISE	2
L00001	LONDON	1
B00002	BANGALORE	3

To implement this is slightly more complicated than the other constraints:
First you create the primary key on the parent table:

SQL> alter table countries add constraint pk_countries primary key (countryid);

Now we create the foreign key constraint on the child table which references the primary key on the parent table:

SQL> alter table cities add constraint fk_cities foreign key (countryid) references countries (countryid);

Time to test: let's try to add a row in Cities with a non-existent Countryid.