Introduction to Constraints

The literal meaning of constraints in English dictionary is Limitation or Restriction and that’s the exact role that Constraints play in Oracle.

Constraints are added to columns in table definitions to specify rules that prevent invalid data from entering into database.

Constraints serve these two important functions in Oracle:

1. Enforcing Strict Rules on Data Inserted in Column: Whenever we try to insert, update or delete data from a row in column, Oracle refers to these rules to ensure the operation we are trying to perform is valid.
2. Constraints also prevents deletion of important rows in a table which have dependencies in some other related table.

The 5 most commonly used Constraints are:

1. Primary Key – Column values must be unique and NOT NULL
2. Foreign Key – Creates Referential Integrity checks where columns in one table has similar values to column in other related table. Ex. Department_ID (Column) in Employees and Departments Table.
3. UNIQUE – Values in Column must be unique. But a column can have multiple rows containing NULL values.
4. NOT NULL – Values in Column cannot contain NULL values.
5. CHECK – Specifies a condition which must be TRUE for all rows.

Data Dictionary For Constraints

Oracle database has several data dictionary views which can be accessed by any user using a simple SELECT query. We can use these data dictionary views to get to know about all constraints present in the Oracle database with all the relevant information we need.

User_Constraints – This Data dictionary view has all the constraints present in Users Schema. Let’s query data from this view:

SELECT * FROM user_constraints;

Result:

This view contains a list of each constraint present in User Schema.

User_Cons_Column – This data dictionary view contains a list of columns on which constraint has been applied.

SELECT * FROM user_cons_columns;

Result:

Natural Join of User_constraints and User_cons_columns

SELECT * FROM user_constraints 
NATURAL JOIN user_cons_columns;

Result:

The natural join of these two data dictionary views is especially helpful when we need to find all the constraints in a specific table. Let’s see one example:

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, 
CONSTRAINT_TYPE, INDEX_NAME, COLUMN_NAME, POSITION
FROM user_constraints 
NATURAL JOIN user_cons_columns
WHERE table_name='EMPLOYEES';

Result:

This query gives us all the information we need about the constraints in ‘Employees’ Table from HR schema.

In this article, we learned about different constraints used in Oracle and how data dictionary views are used to find more information about the constraints present in the Oracle Database.