Constraints In Oracle

In today’s article, we will be talking about how to use Constraints to ensure data integrity in the Oracle Database.

Oracle provides us with great convenience in data integrity with PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK and NOT NULL constraints.

All constraints in the database can be seen in detail from the DBA_CONSTRAINTS, ALL_CONSTRAINTS and USER_CONSTRAINTS views.

The information of some columns in these views is as follows.

Shows the type of Constraint.

C (check constraint on a table),

P (primary key),

U (unique key),

R (referential integrity),

V (with check option, on a view),

O (with read only, on a view)

R_OWNER: Used in constraint types of “Referential Integrity”.

It shows the table to which the column is connected with PK.

R_CONSTRAINT_NAME: Used in constraint types of “Referential Integrity”.

It shows the constraint name of the column to which the column is connected with PK.

DELETE_RULE: Used in constraint types of “Referential Integrity”.

It specifies whether the FK column will be deleted if the PK column is deleted.

DEFERRABLE: Indicates whether the constraint can be deferred.

DEFERRED: Indicates whether the constraint will be deferred at the beginning.

INDEX_OWNER: Used in PK and Unique Indexes.

The name of the user who owns the index is displayed.

INDEX_NAME: Used in PK and Unique Indexes.

The name of the index is displayed.