CONSTRAINTS

Constraints In O R A C L E


Constraints are nothing but conditions given on a column to maintain data integrity within the database. A constraint clause can constrain a single column or group of columns in a table. The point of these constraints is to get oracle to do most of the work in maintaining the integrity of your database. The more constraints you add to a table definition, the less work you have to do in applications to maintain the data. On the other hand, the more constraints there are in a table, the longer it takes to update the data.
There are two ways to specify constraints. As part of the column definition (a column constraint) or at the end of the create table statement (a table constraint). Clauses that constrain several columns must be table constraints. Constraints in Oracle and their purpose are as follows.

1. Not Null : This constraint is used to restrict the user from inserting null values into a column.

2. Unique : This constraint is used to restrict the user from inserting duplicate values to a column.

3. Primary Key : This constraint is used to restrict the user from inserting duplicate values or null values into a column. Another purpose of Primary Key is a column to be referenced by a Foreign Key must be a Primary Key. Oracle allows only one primary key for the table.

4. Foreign Key : This constraint is used to create relationship between two tables. We create relationship between two tables by giving reference to a primary key column of one table to a column in second table, which is called foreign key column. Foreign Key constraint allows either null values or the values present in a Primary Key column to which it refers.
When a relationship is created between two tables then the table that contains Primary Key is called as Master or parent Table and the table that contains Foreign Key is called as Detailed or child Table and the Primary Key and Foreign Key relationship is called as Master Detail Relationship.
A Foreign Key constraint is also called as Referential Integrity constraint. If a Foreign Key refers to a Primary Key present in the same table then that foreign key is called as Self Referential Key.
The clause on delete cascade added to the references clause tells oracle to delete the dependent row when you delete the corresponding row in the parent table. This action automatically maintaince referential integrity.

5. Check : Many columns must have values that are within a certain range or that satisfy certain conditions. A column level check constraint can’t refer to values in other rows. It can’t use the pseudo columns sysdate, uid, user, userenv, currval, nextval, level or rownum. You can use the table constraint form to refer to multiple columns in a check constraint.