Integrity Constraints

An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.

Integrity constraints are defined with a table and are stored as part of the table’s definition, centrally in the database’s data dictionary, so that all database applications must adhere to the same set of rules. If a rule changes, it need only be changed once at the database level and not many times for each application.

The following are the various types of integrity constraints:-

• Domain integrity constraints

• Entity integrity constraints

• Referential integrity constraints

Domain Integrity Constraints #

These constraints set a range, and any violations that take place will prevent the user from performing the manipulation that caused the breach. There are basically two types of domain integrity constraints.

• Not null constraint

• Check constraint

By default the tables can contain null values. The enforcement of Not Null constraints in a table essures that the table contains values. Oracle will not validate the record until this is satisfied. The other type of constraint available under this classification is the ‘check’ constraint. This can be defined to allow only a particular range of values. When the demarcation specified in this range is violated Oracle rejects the record.\

Entity Integrity constraint #

These entity integrity constraints are of two types

• unique constraints

• primary key constraints

The unique constraint designates a column of a group of columns as a unique key. This constraint allows only unique values to be stored in the column Oracle rejects duplication of records when the unique key constraint is used.

The primary key constraint is similar to the unique key constraint. The primary key constraint just like the former avoids duplication of values. Its need is best felt when a relation has to be set between tables, because in addition to preventing duplication it also does not allow null values.

• A unique index is automatically created for a primary key

• A composite primary key is created by using the table level definition

Referential integrity constraint #

• This enforces relationship between tables. It designates a column or combination of columns as a foreign key.

• The foreign key establishes a relationship with a specified primary or unique key in the another table, called the referenced key.

• In this relationship, the table containing the foreign key is called the child table and the table containing the referenced key is called the parent table.

• One can either enable or disable a constraint.

• The former enforces the constraint and the later will not enforce the rule, even though the constraint would remain as it is in the data dictionary.

• By default, the constraint would be enabled.

• Further we can define a constraint either at table or column level.

• On the other hand, if it is defined at the column level, it holds good only for the column for which it is defined.

• Now, let us look at how to impose all these constraints as per the need.

Adding a Constriant by using Alter Command #

Alter table table_name add [constraint constraint_name] constraint_type (column);

Note:

• we can add or drop a constraint,but we cannot modify a constraint.

• We can enable or disable a constraint

• We add a not null constraint by using the modify clause

Add a foreign key constraint to the emp table indicating that a manager must Already exist as a valid employee in the emp table.

SQL> alter table emp add constraint emp_mgr_fk
foreign key (mgr) references emp(empno);

This constraint ensures that a manager exists as a valid employee in the emp table.

Dropping A Constraint #

Remove the manager constraint from the emp table

SQL> alter table emp drop constraint emp_mgr_fk;

Remove the primary key constraint on the dept table and drop the associated foreign key Constraint on the emp.deptno column.

SQL> alter table dept drop primary key cascade;

Disabling Constraints #

Execute the disable clause of the alter table statement to deactivate an integrity constraint

SQL> ALTER TABLE table DISABLE CONSTRAINT constraint CASCADE;

Enabling Constraint #

Activating an integrity constraint currently disabled in the table definition by using The ENABLE clause.

SQL> ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Viewing Constraints #

Query the USER_CONSTRAINTS table to view all constraint definitions and names.

Query the USER_CONS_COLUMNS view columns associated with the constraint names.