Overview on Oracle Constraints

Data Constraints:

To run any business, there should be business data being gathered, stored and analyzed.
So, to maintain the integrity of data, business managers create set of rules, which must be applied to their data prior to being stored in database.
Business rules, which are enforced on data being stored in a table, are called constraints.

Oracle provides SQL syntax to add constraint on table columns and once constraint is created over table, oracle engine starts checking data being inserted/updated into a table column against the constraints.If the data passes this check, it is stored in the column, otherwise data is rejected.
Oracle allows programmers to define constraints at Column level is called inline specification and when define constraints at Table level is called out of line specification.


Types of Constraints:

1. Not Null Constraint: it does not allow null value in a database table.

2. Unique Key Constraint: it allows multiple entries of NULL into the column and does not allow multiple rows having the same value in the same column or combination of columns.

3. Primary Key Constraint: It also prohibits multiple rows having the same value in the same column or combination of columns as unique key constraints but doesn’t allow null values (i.e. column can’t be left blank).

4. Foreign Key Constraint: It represents relationships between tables. Foreign key is column or group of columns whose values are derived from primary key or unique key of some other table.

5. Check Constraints: it applies conditions on each row of a table.


Some Important Facts about Constraints:

1. Not NULL constraints are only applied at column level, i.e. it must be declared inline.
All other constraints can be declared at column/table level, i.e. inline or out of line.

2. Constraints clauses can be seen only in “create table, alter table, create view, alter view” statements.

3. We can enforce constraints on views, only on the basis of constrains on base table. Only unique, primary and foreign key constraints can be enforced in DISABLE NOVALIDATE mode on views.



Source: Internet