What are Oracle Constraints?

Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity). You may also use them to enforce business rules (with some magination).

These constraints can be attribute-based (column), tuple-based (table), key based and referential integrity based.

As Oracle views are always dynamically generated from their base tables, so the view can not contain constraints.

If there is a violation of the constraints caused by some actions performed on the database, then the constraints aborts the action accordingly. The Oracle implementation of constraints differs from the SQL implementation of these constraints.

More on Oracle constraints:

The basic structure of an Oracle constraint is defined as:

The CONSTRAINT keyword is followed by a unique constraint name and then the constraint definition. The constraint name is used to manipulate the constraint once the table has been created.

In Oracle, constraints can be defined at the column or table level. An example of defining constraints at table level may be:

CREATE TABLE STUDENT (
STUDENT _ID NUMBER(3) CONSTRAINT S_ID
CHECK (STUDENT _ID > 0),
STUDENT _NAME CHAR(30) CONSTRAINT S_NAME NOT NULL,
MARKS_COUNT NUMBER(6),
CONSTRAINT STUDENT _PRIME PRIMARY KEY (STUDENT _ID))

We have now created our table with constraints. In this table, in the first attribute definition, after the CONSTRAINT keyword, S_ID is the name of the attribute on which it has to applied and “CHECK” is the type of constraint followed by the definition of that constraint to be followed for this table.

Column level constraints go directly after the column definition to which they refer and the table level constraints go after the last column definition.

CREATE TABLE CLASS (
ROOM NUMBER(10) CONSTRAINT ID CHECK (ID BETWEEN 1 AND 2000),
SUBJECT VARCHAR2(200) CONSTRAINT S_TITLE NOT NULL,
CODE VARCHAR2(50) CONSTRAINT CODE NOT NULL,
ID NUMBER(8,2) DEFAULT 0.00 DISABLE,
CLASS_DATE DATE,
LAB_DATE DATE,
LECT_TAKEN NUMBER(6),
SUBJECT_ID NUMBER(3),
CONSTRAINT ROOM PRIMARY KEY (ISBN),
CONSTRAINT SUBJECT_SCORE FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID))

Table level Oracle constraints are used for compound foreign and prime key definitions.

In the table given above, table level constraints could also have been placed as column definitions.

In a table constraint, you may omit the CONSTRAINT keyword and constraint name if you wish. But if you omit the constraint name then you will have no easy way of enabling / disabling the constraint without deleting the table and rebuilding it.

Oracle does give default names to constraints not explicitly named. This can be checked and verified by selecting from the USER_CONSTRAINTS data dictionary view.

Oracle supports the following constraints on tables and views:

• NOT NULL - This is always inherited directed from the base tables that make-up the view.
• Unique constraints - Oracle9i allows for unique constraints to be defined upon any column of the view.
• Primary key - Today we can get primary key constraints defined directly upon the view.
• Foreign key - Foreign key referential integrity is now directly available whenever a view has foreign key dependencies against other base tables.

Our two example tables do have some rules which need enforcing.

Mainly, both tables have a primary key constraint so that the database doesn't allow replication of data.

Similarly, the Section ID needs to be linked to each book to identify which library section it belongs to and this supports foreign key.

We also want to specify through Oracle constraints which columns must be filled in and which columns have default values for other attributes.

If we wish we can introduce cascading validation and some constraint violation logging to our tables.

CREATE TABLE AUDIT (
ROWID ROWID,
OWNER VARCHAR2,
TABLE_NAME VARCHAR2,
CONSTRAINT VARCHAR2))

CREATE TABLE SECTION (
SECTION_ID NUMBER(3) CONSTRAINT S_ID CHECK (SECTION_ID > 0),
SECTION_NAME CHAR(30) CONSTRAINT S_NAME NOT NULL,
BOOK_COUNT NUMBER(6),
CONSTRAINT SECT_PRIME PRIMARY KEY (SECTION_ID),
EXCEPTIONS INTO AUDIT)

CREATE TABLE BOOK (
ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000),
TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL,
AUTHOR VARCHAR2(50) CONSTRAINT B_AUTH NOT NULL,
COST NUMBER(8,2) DEFAULT 0.00 DISABLE,
LENT_DATE DATE,
RETURNED_DATE DATE,
TIMES_LENT NUMBER(6),
SECTION_ID NUMBER(3),
CONSTRAINT BOOK_PRIME PRIMARY KEY (ISBN),
CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID)
ON DELETE CASCADE)

Oracle does not allow us to delete a section which had books assigned to it as this breaks integrity rules.

If we wanted to get rid of all the book records assigned to a particular section when that section was deleted we could implement a DELETE CASCADE.

The delete cascade operates across a foreign key link and removes all child records associated with a parent record.

ALTER TABLE BOOK ENABLE CONSTRAINT B_AUTH

The above statements demonstrate disabling and enabling a constraint: Note that if, between disabling a constraint and re enabling it, data was entered to the table that included NULL values in the AUTHOR column, then you wouldn't be able to re enable the constraint.

This is because the existing data would break the constraint integrity. You could update the column to replace NULL values with some default and then re enable the constraint.

Return to Oracle Database constraints