Oracle Constraints

Oracle constraints are means in the process of defining some conditions about the database that must remain true while inputting/modifying/deleting data in the database.

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))


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))