Creating and Manipulating Constraints in Oracle Database

Constraints are some rules imposed on the columns of a table to avoid invalid data entry.

The different types of constraints available are:

  • Primary Key
  • Not Null
  • Unique
  • Check
  • Foreign Key

Primary Key

This constraint avoids null values and duplicate values in a column. It is used to uniquely identify a record in a table.

Not Null

This constraint avoids null values in a column. It makes sure that null values cannot be inserted into the column.

Unique

This constraint avoids duplicate entries in a column. A column having NOT NULL constrain can however have multiple NULL values.

Check

This constraint is used for condition-wise data entry into a column. Example: If we want only numbers between 100-1000 should be allowed to be inserted into a particular column, we can use check constraint on the table to achieve this.

Foreign Key

This constraint is used to establish a relation between two or more table using a common attribute(column). It always make a relationship using the primary key.

Note: Foreign key supports the concept of referential integrity, i.e. No child records can be inserted into the child table unless the master table is available.


Imposing Constraints during Table Creation

Constrains can be imposed on a column during the creation of the table. This can be done at two levels:

  • Column Level
  • Table Level

The basic difference between column level constraint & table level constraint is that in column level maximum one constraint can be imposed at one point of time on a column, whereas in table level multiple constraints can be imposed at one point of time on a column.

Not Null Constraint cannot be used in table level.

Syntax to impose column level constraint:
CREATE TABLE <TABLE_NAME>
(<COL_NAME> <DATATYPE> (<SIZE>)
[CONSTRAINT <CONSTRAINT_NAME>] <CONSTRAINT_TYPE>,
<COL_NAME> <DATATYPE> (<SIZE>)
[<CONSTRAINT> <CONSTRAINT_NAME>]<CONSTRAINT_TYPE> ... N);
Syntax to impose table level constraint:
CREATE TABLE <TABLE_NAME>
(<COL_NAME> <DATATYPE> (<SIZE>),
<COL_NAME> <DATATYPE> (<SIZE>),
[CONSTRAINT <CONSTRAINT_NAME>] <CONSTRAINT_TYPE>,
[<CONSTRAINT> <CONSTRAINT_NAME>]<CONSTRAINT_TYPE> ... N);
Example:
CREATE TABLE EMPLOYEE
(EMPNO NUMBER (4) CONSTRAINT EMPOYEE_EMPNO_PK PRIMARY KEY,
NAME VARCHAR2 (10) CONSTRAINT EMPLOYEE_NAME_NT NOT NULL,
SAL NUMBER (7, 2) CONSTRAINT EMPLOYEE_SAL_CK CHECK (SAL BETWEEN 2000 AND 10000),
JOB VARCHAR2 (10) CONSTRAINT EMPLOYEE_JOB_UK UNIQUE);

Above statement is an example of column level constraint. The primary key constraint is imposed on empno i.e. this column will uniquely identify a record in employee table, not null is imposed on name i.e. name cannot be null, check is imposed on salary, it should be between 2000 and 10000, and job has unique key imposed which means job must be unique.

If any of the constraint is violated, the operation will fail and an error will be reported to the user.

Below example shows a table level constraint:

CREATE TABLE EMPL
(EMPNO NUMBER (4),
NAME VARCHAR2 (10) 
CONSTRAINT EMPL_NAME_NT NOT NULL,
SAL NUMBER (7, 2),
JOB VARCHAR2 (10),
CONSTRAINT EMPL_EMPNO_PK PRIMARY KEY (EMPNO),
CONSTRAINT EMPL_SAL_CK CHECK (SAL BETWEEN 2000 AND 10000),
CONSTRAINT EMPL_JOB_UK UNIQUE (JOB);

Not Null is imposed at column level since it is not allowed at table level.


Imposing Foreign Key Constraints

CREATE TABLE EMPL
(EMPNO NUMBER (4)
ENAME VARCHAR2 (10) CONSTRAINT EMPL_ENAME_NT NOT NULL,
SAL NUMBER (7, 2) CONSTRAINT EMPL_SAL_NY NOT NULL,
DJOIN DATE CONSTRAINT EMPL_DJOIN_NT NOT NULL,
MGR NUMBER (4),
DEPTNO NUMBER (4),
CONSTRAINT EMPL_EMPNO_PK PRIMARY KEY (EMP NO),
CONSTRAINT EMPL_SAL_CK CHECK (SAL BETWEEN 2000 AND 10000),
CONSTRAINT EMPL_DJOIN_CK CHECK (DJOIN > TO_DATE (’01-JAN-2000’))
CONSTRAINT EMPL_MGR_FK FOREIGN KEY (MGR) REFERENCES EMPL (EMPNO),
CONSTRAINT EMPL_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

In the above example, the first foreign key establishes a relationship with itself. As we stated earlier foreign key constraint must point to the primary key. In this can "EMPNO" is the primary key of the table. The second foreign key establish a relationship with a table DEPT, DEPTNO should be the primary key of DEPT.


On-Delete Cascade

On-delete cascade is a keyword used while imposing foreign key constraint on a column to enforce a rule that whenever a master record is deleted all corresponding child records gets automatically deleted.

Example:
CREATE TABLE EMP (EMPNO NUMBER (4), NAME VARCHAR2 (10), SAL NUMBER (7, 2), DEPTNO NUMBER (4) CONSTRAINT EMP_DEPTNO_FK REFERENCES DEPT(DEPTNO) ONDELETE CASCADE);

Adding Constraint to an Existing Table

Any constraint can be added to a column after table creation except NOT NULL, to add NOT NULL the column is needed to be modified.

Syntax:
ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <CONSTRAINT_NAME> <CONSTRAINT TYPE>(<COLUMN_NAME>);
CONSTRAINT <CONSTRAINT_NAME> <CONSTRAINT TYPE>(<COLUMN_NAME>) ... N;
Examples:
ALTER TABLE EMP
ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO);

ALTER TABLE SYSEMPL4
ADD CONSTRAINT EMP_SAL_CK CHECK (SAL BETWEEN 2000 AND 10000);

Adding NOT NULL by modifying column:
ALTER TABLE EMP
MODIFY (NAME VARCHAR2 (10) CONSTRAINT SYSEMPL4_NAME_NT NOT NULL);

Note: A constraint can be added or deleted, but it can't be modified.


Deleting a Constraint

Syntax:
ALTER TABLE <TABLE_NAME>
DROP CONSTRAINT <CONSTRAINT_NAME>;
Examples:
ALTER TABLE EMP DROP CONSTRAINT EMP_EMPNO_PK;

Activate/Deactivate a Constraint

Any constraint can be enabled or disabled using the ENABLE/DISABLE command.

ALTER TABLE <TABLE_NAME>
ENABLE/DISABLE CONSTRAINT <CONSTRAINT_NAME>;
POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article