Constraints in SQL

by Shikha Katariya · August 21, 2015

What are Constraints ??

Constraints may be defined as rules or conditions applied on the data columns of the table, that restrict the type of data entering into the table.They act as filter between the data and the table.

Levels of Constraints :

1. Column Level – Applied to single column of the table.E.g Not Null , Primary Key etc.

2. Table Level – Applied to multiple columns. E.g. Primary Key , Unique Key etc.

Primary Key Constraint work at both Column and Table Level.
For each constraint , oracle database creates a Unique Constraint Name E.g SYS_COO7229…etc.

Syntaxes :

1. To View all the Constraints definitions of all the tables which are accessible to Current User in Current User’s Own Schema :

SELECT * FROM USER_CONSTRAINTS ;

2. To View all the Constraints definitions of a particular table of Current User in Current User’s Own Schema :

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘<TABLE NAME’>;

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = '<TABLE NAME>' ;

Note : TABLE_NAME keyword should be given in caps as it represents a column and table name should    also be given in caps because internally database stores in upper case.

To View all the Constraints definitions of all the tables which are accessible to Current User present in Current User’s Own Schema as well as other User’s Schema :

SELECT * FROM ALL_CONSTRAINTS;

To View all the Constraints definitions of a particular table of Current User present in Current User’s Own Schema as well as other User’s Schema :

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = '<TABLE NAME>';

Note : TABLE_NAME keyword should be given in caps as it represents a column and table name should    also be given in caps because internally database stores in upper case.

To View all the Constraint definitions of all the tables present in all Schemas :

SELECT * FROM DBA_CONSTRAINTS ;

Note : User_Constraints , DBA_Constraints and ALL_Constraints , These all are Data Dictionary Views.

Defining Constraints :

Constraints are defined at the time of Table Creation.

/************Syntax for Defining the Constraints **************/

CREATE TABLE <Tablename> 
(
  <Col Name1> datatype(size) <Constraint Name>,
  <Col Name2> datatype(size) <Constraint Name>,
  <Col NameN> datatype(size) <Constraint Name>
);

Modifying Constraints :

Constraints can be modified using ALTER Command.

Adding Constraint after Creating the table :

/***************Syntax for Adding Constraint after Creating the table **********/

ALTER TABLE <Tablename> 
ADD CONSTRAINT <Constraint name> <Constraint type>(Col Name);

Renaming the Constraint :

/*************** Syntax to Rename the Constraint ***********************/

ALTER TABLE <Tablename>
RENAME CONSTRAINT <oldConstraintname> TO <newConstraintname>;

Dropping the Constraint :

/******************* Syntax for Dropping the Constraint *********************/

ALTER TABLE <Tablename>
DROP CONSTRAINT <constraint Name>;

Enabling the Constraint :

/******************* Syntax for Enabling the Constraint *****************/

ALTER TABLE <Tablename>
ENABLE CONSTRAINT <Constraint Name>;

Disabling the Constraint :

/***************** Syntax for Disabling the Constraint **********************/

ALTER TABLE <Tablename>
DISABLE CONSTRAINT <Constraint name>;

Example Illustration :

Note : Types of constraints will be covered in next post , here we have used the names just to explain.

/**************** Example for usage of all above Commands *********************/

1. Let's Create a Table 'Sample' having three columns - ID ,AcctNo and Name and Unique Constraint
defined on ID column:

CREATE TABLE SAMPLE (ID NUMBER(5),ACCTNO NUMBER(15),UNIQUE, NAME VARCHAR2(10));

Output: Table Created
-----------------------------------------------------------------------------------------------------

2. Adding a Constraint after table Creation - suppose I want to add Not null Constraint to ACCTNO
column.Suppose we keep SAMPLE_ACCTNO_NN as constraint name.

ALTER TABLE SAMPLE
ADD CONSTRAINT SAMPLE_ACCTNO_NN NOT NULL(ACCTNO);

Output: Table Altered
-----------------------------------------------------------------------------------------------------

3. Renaming the Constraint - Let's change the old constraint name 'SAMPLE_ACCTNO_NN' to new constraint
name SAMPLE1_ACCT_NN.

ALTER TABLE SAMPLE
RENAME CONSTRAINT SAMPLE_ACCTNO_NN TO SAMPLE1_ACCT_NN;

Output: Table Altered.
-----------------------------------------------------------------------------------------------------

4. Dropping the Constraint : Let's drop the constraint 'SAMPLE1_ACCT_NN'.

ALTER TABLE SAMPLE
DROP CONSTRAINT SAMPLE1_ACCT_NN;

Output: Constraint Dropped.
--------------------------------------------------------------------------

5. Disabling the Constraint - Suppose we wanna disable 'Unique Constraint' of ID column.
Now here the first challenge is to know the system defined name of the constraint.

/*****************Fetching the Constraint name **************/

SELECT * FROM USER_CONSTRAINTS WHERE TABLE = 'SAMPLE';

/****************It will somewhat give the following output **************/

SYS_C007221 | U | "ID" is Unique......few more details.

From the above output we will come to know the system generated constraint name.

/**************** Disable the constraint now *********************/

ALTER TABLE SAMPLE
DISABLE CONSTRAINT SYS_C007221;
--------------------------------------------------------------------------------------------

/****************Enable the constraint now ***********************/

ALTER TABLE SAMPLE
ENABLE CONSTRAINT SYS_C007221;

Advantages of Using Constraints :

They help in maintaining integrity of the data.
They make the data in the database more reliable.