| Team Fly | |
There are a number of different types of integrity constraints. The following is a list of the integrity constraints that are available in the Oracle database:
NULL constraints are defined on a single column and dictate whether or not the column must contain a value. If a column is defined as NOT NULL, it must contain values in each and every record.
UNIQUE constraints allow a value in a column to be inserted or updated providing it contains a unique value.
PRIMARY KEY constraints require that the key uniquely identifies each row in the table. The key may consist of one column or a combination of columns.
FOREIGN KEY constraints define the relationships between tables. This is commonly referred to as referential integrity. These are rules that are based on a key in one table that assure that the values exist in the key of the referenced table.
CHECK constraints enable users to define and enforce rules on columns. Acceptable values are defined for a column and insert, update, and delete commands are interrogated and are accepted or rejected based on whether or not the values are specifically allowed. A separate check constraint definition is required if the requirement exists to perform either similar or different checks on more than one column. The following example illustrates the creation of a table with a single check constraint, followed by an insert with an acceptable value and an attempted insert with a disallowed value:
SQL> create table check_constraint_example
2 (col1 char(1)
3 constraint check_col1
4 check (col1 in ('B','G','N')));
Table created.
SQL> insert into check_constraint_example values ('B');
1 row created.
SQL> insert into check_constraint_example values ('C');
insert into check_constraint_example values ('C')
*
ERROR at line 1:
ORA-02290: check constraint (SH.CHECK_COL1) violated
| Team Fly | |