Understanding Oracle 11g Constraints

By Contributing Author on September 26, 2011 in Database Development, Oracle Database

Introduction to Constraints

Oracle constraints are defined as the rules to preserve the data integrity in the application. These rules are imposed on a column of a database table, so as to define the basic behavioral layer of a column of the table and check the sanctity of the data flowing into it.
The data which violates the rule, fails to pass the constraint layer and oracle raises a predefined exception. The integrity layer can be further expanded programmatically through triggers and validation subprograms as per the application requirements and standards.
Constraints establish an underlying business nature of data like its uniqueness, its references, NULL behavior or domain oriented limits. The key milestones achieved by the usage of constraints are:

Validate NULL property of the data
Validate uniqueness of the data
Validate referential integrity of the data

Constraint: Common Usage Guidelines

Before I dig into complete understanding, I shall list the common usage guidelines of constraints.

Constraints can be imposed on a single column or group of columns.
Constraints can be defined at column level or table level (except NOT NULL). ‘Column level’
Constraint definition means that the constraint would be associated directly and adjacently to the column. ‘Table level’ constraint definition starts once all the columns are already declared.
Constraints are not only effective while data creation, but also during data modification and deletion.
Constraints can be explicitly added on a table column using ALTER TABLE command
Constraints can be disabled if not required, especially during bulk loads; later they can be re-enabled.

The figure below lists the types of constrains available in Oracle. We shall discuss each one of them in detail.

NOT NULL Constraint

The NOT NULL constraint restricts prevents the inclusion of NULL values in a column. If any NULL values are encountered during insertion, Oracle raises exception ‘ORA-01400: cannot insert NULL into [Column description]’. NOT NULL constraint is also active during update operation; violation of the rule results in exception ‘ORA-10407: cannot update [Column description] to NULL’.

Syntax

[COLUMN NAME] [DATA TYPE] [CONSTRAINT (CONSTRAINT NAME)] [NOT NULL] In the syntax, [CONSTRAINT (CONSTRAINT NAME)] are optional.
Examples:
–A table NN_DEMO is created with NOT NULL Constraint by name.–

CREATE TABLE NN_DEMO

(A NUMBER CONSTRAINT CONS_NN_DEMO_A NOT NULL);

Table created.

–A table NN_DEMO is created with NOT NULL Constraint without any name.–

CREATE TABLE NN_DEMO (A NUMBER NOT NULL);

Table created.

‘Not Null’ constraint can be defined only at the column level. However, there is no specific exception for the syntax violation in this case.
–A table NN_DEMO is created with NOT NULL at table level. However, there is no specific exception defined for this violation.–

CREATE TABLE NN_DEMO

(A NUMBER,

NAME VARCHAR2(100),

CONSTRAINT CONS_NN_DEMO_A NOT NULL(A))

/

CONSTRAINT CONS_NN_DEMO_A NOT NULL(A)) ERROR at line 4: ORA-00904: : invalid identifier

UNIQUE Constraint

As the name suggests, the constraint enforces barrier on the uniqueness of the data. It prevents duplication of the column data, but interestingly allows NULLs. It allows multiple NULLs during creation as well as modification of the column data.

Syntax

Column Level:
COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]. At column level, CONSTRAINT keyword and CONSTRAINT_NAME are optional.
Table Level:
CONSTRAINT [constraint name] UNIQUE (column name).At table level, CONSTRAINT keyword and CONSTRAINT_NAME are mandatory.

Examples

—–Demonstrate column level definition of unique constraint—–

CREATE TABLE UNIQUE_DEMO_COL 2 (ID NUMBER, NAME VARCHAR2(100) UNIQUE);

Table created.
—–Demonstrate Insertion of Unique Values in the table—–

INSERT INTO UNIQUE_DEMO_COL VALUES(1,'Name 1');

1 row created.
—–Demonstrate Violation of Unique constraint—–

INSERT INTO UNIQUE_DEMO_COL VALUES(2,'Name 1');

ERROR at line 1:ORA-00001: unique constraint (SCOTT.SYS_C0011341) violated
—–Demonstrate Table level definition of unique constraint—–

CREATE TABLE UNIQUE_DEMO (ID NUMBER, NAME VARCHAR2(100),

CONSTRAINT UN_UNIQUE_DEMO_ID UNIQUE(ID));

Table created.
—–Display constraint metadata. Note the Index created—–

SELECT constraint_name, constraint_type, index_name from user_constraints

where table_name='UNIQUE_DEMO';

—–Demonstrate association of an existing index with Unique Key—–

CREATE TABLE UNIQUE_DEMO 2 (ID NUMBER, NAME VARCHAR2(100));

Table created.
—–Creating a Unique Index on ID column—–

CREATE UNIQUE INDEX IDX_UNIQUE_DEMO ON UNIQUE_DEMO(ID);

Index created.
—–Addition of Unique constraint—–

ALTER TABLE UNIQUE_DEMO ADD CONSTRAINT UN_UNIQUE_DEMO_ID UNIQUE(ID);

Table altered.
—–Verify the association of Index with the unique constraint—–

SELECT constraint_name, constraint_type, index_name from user_constraints

where table_name='UNIQUE_DEMO';

Primary Key Constraint

As known by its name, Primary key is the ‘most premier’ column of a table. It strictly allows only unique and definite values in the column. Often it is referred to as the hybrid evolution of NOT NULL and UNIQUE, but with different logical and physical implications.
A column or group of columns of a table can be declared as Primary Key of the table. Composite primary keys can accommodate maximum of 32 columns. The declaration can be either made at Column level, Table level or using ALTER TABLE command.
Unlike other constraints, there can be one and only one Primary Key in a table. It can be either a single column or a composite primary key. Oracle raises the exception ‘ORA-02260: table can have only one primary key’, if one attempts to duplicate the primary key in a table.

Syntax

Column level:
COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY] At column level, CONSTRAINT keyword and CONSTRAINT_NAME are optional.
Table level:
CONSTRAINT [constraint name] PRIMARY KEY [column (s)]
At column level, CONSTRAINT keyword and CONSTRAINT_NAME are mandatory. Addition of system generated Primary Key

ALTER TABLE [TABLE NAME] ADD PRIMARY KEY [COLUMN NAME]

Above ALTER TABLE command creates a primary key with a system generated name of format SYS_CXX.
Similar to UNIQUE key, a unique b-tree index is always created whenever a primary key is created, with the same name as that of primary key constraint. Remember, a unique key cannot be promoted as the primary key in a table.

Examples

—–Demonstrate column level definition of Primary key constraint—–

CREATE TABLE PK_DEMO_1 2 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(100));

Table created.
—–Verify Primary key metadata from USER_CONSTRAINTS. Note that the GENERATED column denotes whether the constraint name has been SYSTEM generated or USER given—–

SELECT CONSTRAINT_NAME, INDEX_NAME, GENERATED

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='PK_DEMO_1';

—–Demonstrate Table level definition of Primary key constraint—–

CREATE TABLE PK_DEMO_2 2 ( ID NUMBER, NAME VARCHAR2(100),

CONSTRAINT PK_ID PRIMARY KEY(ID));

Table created.
—–Verify Primary key metadata in USER_CONSTRAINTS. Note the different value for GENERATED column—–

SELECT CONSTRAINT_NAME, INDEX_NAME, GENERATED FROM USER_CONSTRAINTS WHERE TABLE_NAME='PK_DEMO_2';

—–Demonstrate manual addition of Primary key using ALTER TABLE command—-

CREATE TABLE PK_DEMO_3 (ID NUMBER, NAME VARCHAR2(100));

Table created.

ALTER TABLE PK_DEMO_3 ADD PRIMARY KEY (ID); Table altered.

SELECT CONSTRAINT_NAME, INDEX_NAME, GENERATED FROM USER_CONSTRAINTS WHERE TABLE_NAME='PK_DEMO_3';

If an index already exists on a column and primary key is created on the same column, the primary key gets associated with the same index.

Foreign Key Constraint

Referential integrity is one of the key data features in a normalized database. Two tables can be connected through a column, where one table acts as Parent table while the other one is the child table.
The key column value set in the child table is always the subset of key column value set in the parent table, thus establishing the Parent Child relationship and obeys the referential integrity of data.
The key column in child table is known as Foreign Key i.e. its data references an ‘external or foreign’ set of values.
Now, Oracle follows certain guidelines to establish this relationship.

The key column in the Parent table must be a Primary Key.
Multiple Child key columns can refer single Parent key column.
Though the Parent table key column is a Primary Key (which does not allows NULLs), foreign key can accommodate NULL values.
Oracle prevents the deletion of a Parent record, if its corresponding child exists in the child table.
If the constraint is enforced with ON DELETE CASCADE option, then the child record would also be deleted.
If the constraint is enforced with ON DELETE SET NULL option, then the child record would not be deleted, but their key column value would be set to NULL
Oracle prevents the creation of a Child record, for which value of key column does not exists in the Parent table.

Syntax

Column Level:
COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]
[CONSTRAINT] and [constraint name] are optional keywords during Column level declaration.
Table level:
CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

Examples

—–Creating a Parent table with a Primary key—–
CREATE TABLE FK_PARENT (PID NUMBER PRIMARY KEY);
Table created.
—–Verify the Primary Key constraint metadata—–
SELECT CONSTRAINT_NAME, INDEX_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME=’FK_PARENT’;
—–Querying the FK_PARENT table to check the value sets—–

SELECT * FROM FK_PARENT;

—–Demonstrate Column level definition of foreign key constraint—–

CREATE TABLE FK_DEMO_1 (CID NUMBER REFERENCES FK_PARENT(PID));

Table created.
—–Multiple foreign keys can refer the same Parent key—–

CREATE TABLE FK_DEMO_2 (CID NUMBER REFERENCES FK_PARENT(PID));

Table created.
—–Verify the FK constraint metadata in USER_CONSTRAINTS view—–

SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME, DELETE_RULE, GENERATED FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('FK_DEMO_1','FK_DEMO_2');

—–Demonstrate table level definition of foreign key constraint—–
CREATE TABLE FK_DEMO_3 2 (CID NUMBER,NAME VARCHAR2(1000),
CONSTRAINT FK_CID FOREIGN KEY (CID) REFERENCES FK_PARENT(PID));
Table created.
—–Demonstrate sample data insertion in the Child Table—–
INSERT INTO FK_DEMO_3 VALUES (1,’Insert 1′);
1 row created.
—–Demonstrate violation of foreign key constraint—–
INSERT INTO FK_DEMO_3 VALUES (20,’Insert 20′); INSERT INTO FK_DEMO_3 VALUES (20,’Insert 20′)
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_CID) violated – parent key not found
—–Exceptionally, NULL is an accepted value in foreign key—–
INSERT INTO FK_DEMO_3 VALUES (null,’Insert 20′); 1 row created.

CHECK Constraint

Till now, we had discussed the constraints which mentor the nature of data flowing into a table column. Now we would see upon the CHECK constraint which monitors the physical data to enforce domain integrity rule on a column.
The CHECK constraint logically fixes the column value by using expressions. If the incoming value satisfies the condition in the expression, it passes through for insertion; in opposite cases, constraint violation exception is raised.
The CHECK constraint expression must use the columns of the same table. Like other constraints, CHECK constraint specification can either be made inline at the column level or can be given at the table level.
Syntax
Column level:
COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]
[CONSTRAINT] and [constraint name] are optional keywords during Column level declaration.
Table level:
CONSTRAINT [name] CHECK (condition)
Additionally, CHECK constraint specification has below restrictions while its specification.

Subqueries expressions cannot be used within CHECK expression
Pseudo columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM are not allowed since their value is dynamic
User defined functions and Deterministic functions like CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP,SESSIONTIMEZONE,SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV
Composite data types or collections

Examples

—–Demonstrate Column level definition of Check constraint—–

CREATE TABLE CK_DEMO_1 (ID NUMBER, NAME VARCHAR2(100), EMPLOYED VARCHAR2(1) CHECK (EMPLOYED IN ('Y','N')) );

Table created.
—–Verify Check constraint metadata in USER_CONSTRAINTS table—–

SELECT constraint_name, search_condition, generated from user_constraints

where table_name='CK_DEMO_1';

—–Demonstrate the insertion

INSERT INTO CK_DEMO_1 VALUES (1, 'JOHN','Y');

1 row inserted.
—–Demonstrate the violation

INSERT INTO CK_DEMO_1 VALUES 2 (2, 'KATE','A');

ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0011477) violated
—–Demonstrate table level definition of foreign key constraint—–

CREATE TABLE CK_DEMO_1 (ID NUMBER, NAME VARCHAR2(100), EMPLOYED VARCHAR2(1),

CONSTRAINT CK_EMPLOYED_YN CHECK (EMPLOYED IN ('Y','N')) );

Table created.
—–Demonstrate the restriction of UDF in Check constraint expressions—–

CREATE OR REPLACE FUNCTION CHECK_RANGE RETURN NUMBER

IS

BEGIN

RETURN 100;

END;

CREATE TABLE CK_DEMO_FUN 2 (ID NUMBER,

NAME VARCHAR2(100), AGE NUMBER CHECK (AGE < CHECK_RANGE));

)

ERROR at line 5:
ORA-02438: Column check constraint cannot reference other columns

Constraints: Common Operations

Dropping a Constraint

A constraint can be dropped from a table using ALTER TABLE command. Syntax for the same is as below

ALTER TABLE [TABLE NAME] DROP CONSTRAINT [CONSTRAINT NAME]

For Example, dropping the CHECK constraint from CK_DEMO_1 would be done as below. SQL> alter table ck_demo_1 drop constraint ck_employed_yn;
Table altered.

Enabling/Disabling a Constraint

At times, a constraint can be enabled or disabled. Usually, such activities are carried out during data loading from legacy systems. We shall take up a small scenario to illustrate it.
—–Verify the data in CK_DEMO_1—–

SELECT * FROM CK_DEMO_1;

—–Disable the CHECK constraint in table CK_DEMO_1—–

ALTER TABLE CK_DEMO_1 DISABLE CONSTRAINT CK_EMPLOYED_YN;

Table altered.
—–Inserting a sample data which violates the CHECK constraint expression. The data is successfully inserted—–

INSERT INTO CK_DEMO_1 VALUES (3, NEL','A');

1 row created.
—–Verify the above insertion in the table—–

SELECT * FROM CK_DEMO_1;

—–Re-enabling the CHECK constraint raises exception, since the table has the violating data—–

ALTER TABLE CK_DEMO_1 ENABLE CONSTRAINT CK_EMPLOYED_YN;

ORA-02293: cannot validate (SCOTT.CK_EMPLOYED_YN) – check constraint violated
—–Deleting the violating data from the CK_DEMO_1 table—–

DELETE FROM CK_DEMO_1 WHERE EMPLOYED='A';

ERROR at line 1: 1 row deleted.
—–Enabling the constraint successfully—–

ALTER TABLE CK_DEMO_1 ENABLE CONSTRAINT CK_EMPLOYED_YN; Table altered.

Conclusion: Seeing the constraints ahead

Constraints are the first step to impose the integrity rules on the data. They not only check the sanctity of the data, but also define a high level data behavioral layer.
Later versions of Oracle have also introduced data types (POSITIVEN, SIMPLE_INTEGER) which have NOT NULL behavior exhibited in their primitive definitions. These recent developments have moved NOT NULL to an obsolete category.
Over the years, Primary key has emerged to be an important aspect of table designing exercise and data storage philosophies.
The paper covers the basics of constraints available in database. I hope the readers to try their hands out with more concrete scenarios of constraints.

Phạm Văn Hiếu

Thứ Tư, 12 tháng 3, 2014