Before I dig into complete understanding, I shall list the common usage guidelines of constraints.
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.–
1
2
|
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.–
1
|
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.–
1
2
3
4
5
|
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—–
1
|
CREATE TABLE UNIQUE_DEMO_COL 2 (ID NUMBER, NAME VARCHAR2(100) UNIQUE);
|
Table created.
—–Demonstrate Insertion of Unique Values in the table—–
1
|
INSERT INTO UNIQUE_DEMO_COL VALUES(1,'Name 1');
|
1 row created.
—–Demonstrate Violation of Unique constraint—–
1
|
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—–
1
2
|
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—–
1
2
|
SELECT constraint_name, constraint_type, index_name from user_constraints
where table_name='UNIQUE_DEMO';
|
—–Demonstrate association of an existing index with Unique Key—–
1
|
CREATE TABLE UNIQUE_DEMO 2 (ID NUMBER, NAME VARCHAR2(100));
|
Table created.
—–Creating a Unique Index on ID column—–
1
|
CREATE UNIQUE INDEX IDX_UNIQUE_DEMO ON UNIQUE_DEMO(ID);
|
Index created.
—–Addition of Unique constraint—–
1
|
ALTER TABLE UNIQUE_DEMO ADD CONSTRAINT UN_UNIQUE_DEMO_ID UNIQUE(ID);
|
Table altered.
—–Verify the association of Index with the unique constraint—–
1
2
|
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
1
|
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—–
1
|
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—–
1
2
3
|
SELECT CONSTRAINT_NAME, INDEX_NAME, GENERATED
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='PK_DEMO_1';
|
—–Demonstrate Table level definition of Primary key constraint—–
1
2
|
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—–
1
|
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—-
1
|
CREATE TABLE PK_DEMO_3 (ID NUMBER, NAME VARCHAR2(100));
|
Table created.
1
2
3
|
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—–
—–Demonstrate Column level definition of foreign key constraint—–
1
|
CREATE TABLE FK_DEMO_1 (CID NUMBER REFERENCES FK_PARENT(PID));
|
Table created.
—–Multiple foreign keys can refer the same Parent key—–
1
|
CREATE TABLE FK_DEMO_2 (CID NUMBER REFERENCES FK_PARENT(PID));
|
Table created.
—–Verify the FK constraint metadata in USER_CONSTRAINTS view—–
1
|
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—–
1
|
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—–
1
2
|
SELECT constraint_name, search_condition, generated from user_constraints
where table_name='CK_DEMO_1';
|
—–Demonstrate the insertion
1
|
INSERT INTO CK_DEMO_1 VALUES (1, 'JOHN','Y');
|
1 row inserted.
—–Demonstrate the violation
1
|
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—–
1
2
|
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—–
1
2
3
4
5
6
7
8
|
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
1
|
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—–
—–Disable the CHECK constraint in table CK_DEMO_1—–
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—–
1
|
INSERT INTO CK_DEMO_1 VALUES (3, NEL','A');
|
1 row created.
—–Verify the above insertion in the table—–
—–Re-enabling the CHECK constraint raises exception, since the table has the violating data—–
1
|
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—–
1
|
DELETE FROM CK_DEMO_1 WHERE EMPLOYED='A';
|
ERROR at line 1: 1 row deleted.
—–Enabling the constraint successfully—–
1
|
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.
