Oracle Constraint

The constraint in the Oracle database is used to prevents or stops invalid data entry into the tables. It ensures the accuracy and reliability of the data in the table. Constraints are created on columns of the table.

Oracle Constraint Types

Oracle has the following types of constraints:-
1) NOT NULL
2) UNIQUE
3) PRIMARY KEY
4) FOREIGN KEY
5) CHECK

These constraint can be created in two ways. These are:- Column level and Table level

Column level

In this method we are defining constraints on individual columns. Each column will have its own constraint.

Syntax:-
CREATE TABLE tableName (
col1 datatype(size) constraintType,
col2 datatype(size) constraintType,
……
);

Table level

In this method, we are defining constraints on a group of columns. Here first we are defining all columns and then we are specifying constraint type along with a group of columns.

Syntax:-
CREATE TABLE tablename(
col1 datatype(size),
col2 datatype(size),
….. ,
constraintType(col1, col2, col3,..…)
);

Among these five constraints, except NOT NULL constraints all other constraints can be created on both column level and table level.

NOT NULL Constraint in Oracle

NOT NULL constraint properties,

• The applied column doesn’t accept NULL values.
• It can be created only on the column level, not on the table level.
• A table can have multiple NOT NULL constraint, but one NOT NULL constraint on one column basis.

Example:-

SQL> CREATE TABLE nulltest(
       ID NUMBER(10) NOT NULL,
       name VARCHAR2(15) NOT NULL,
       address VARCHAR2(15)
     );
Table created.

The table “nulltest” has three columns:- id, name, and address. The id and name columns having NOT NULL constraint, therefore it won’t accept null values. But the address column doesn’t have any such type of restrictions.

When column have NOT NULL constraint then it won’t accept null value, but it will accept duplicate values.

SQL> INSERT INTO nulltest VALUES(1, 'abc', 'xyz');
1 row created.

SQL> INSERT INTO nulltest VALUES(1, 'abc', NULL);
1 row created.

SQL> SELECT * FROM nulltest;
        ID NAME            ADDRESS
---------- --------------- ---------------
         1 abc             xyz
         1 abc

SQL> INSERT INTO nulltest VALUES(1, NULL, 'xyz');
ORA-01400: cannot insert NULL into ("SCOTT"."NULLTEST"."NAME")

SQL> INSERT INTO nulltest VALUES(NULL, 'abc', 'xyz');
ORA-01400: cannot insert NULL into ("SCOTT"."NULLTEST"."ID")

SQL> INSERT INTO nulltest VALUES(NULL, NULL, 'xyz');
ORA-01400: cannot insert NULL into ("SCOTT"."NULLTEST"."ID")

UNIQUE Constraint in Oracle

Properties of Unique Constraint,

• It doesn’t accept duplicate values.
• It can be created on both column level and table level.
• In a table, there can be multiple UNIQUE constraints.

Example on column level UNIQUE constraint,

In column level, different columns will have its own unique constraint.

SQL> CREATE TABLE uniquetest(
       id NUMBER(10) UNIQUE,
       name VARCHAR2(15) UNIQUE,
       address VARCHAR2(15)
     );
Table created.

The table “uniquetest” having three columns:- id, name, and address. Since id and name columns having unique constraints so they won’t accept duplicate data but they will accept null values. The address column doesn’t have any restrictions.

SQL> INSERT INTO uniquetest VALUES(1, 'abc', 'pqr');
SQL> INSERT INTO uniquetest VALUES(2, NULL, 'pqr');
SQL> INSERT INTO uniquetest VALUES(NULL, 'uvw', 'pqr');
SQL> INSERT INTO uniquetest VALUES(NULL, NULL, 'pqr');
SQL> SELECT * FROM uniquetest;
        ID NAME            ADDRESS
---------- --------------- ---------------
         1 abc             pqr
         2                 pqr
           uvw             pqr
                           pqr

SQL> INSERT INTO uniquetest VALUES(1, 'abc', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014352) violated

SQL> INSERT INTO uniquetest VALUES(1, 'xyz', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014352) violated

SQL> INSERT INTO uniquetest VALUES(9, 'abc', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014353) violated

Example on table level,

In table level, one unique constraint is applied on the combination of multiple columns of a table.

SQL> CREATE TABLE uniquetest1(
       id NUMBER(10),
       name VARCHAR2(10),
       addrs VARCHAR2(10),
       UNIQUE(id, name)
     );
Table created.

In the uniquetest1 table, there is only one UNIQUE constraint that is applied to the combination of id and name columns. Therefore, the combination of id and name values should be unique else table won’t accept the data.

SQL> INSERT INTO uniquetest1 VALUES(1, 'abc', 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(1, 'xyz', 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(2, 'xyz', 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(2, 'abc', 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(NULL, 'abc', 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(2, NULL, 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(NULL, NULL, 'pqr');
SQL> INSERT INTO uniquetest1 VALUES(NULL, NULL, 'pqr');
SQL> SELECT * FROM uniquetest1;
        ID NAME       ADDRS
---------- ---------- ----------
         1 abc        pqr
         1 xyz        pqr
         2 xyz        pqr
         2 abc        pqr
           abc        pqr
         2            pqr
                      pqr
                      pqr

8 rows selected.

SQL> INSERT INTO uniquetest1 VALUES(1, 'abc', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014354) violated

Primary key Constraint in Oracle

The properties of Primary key constraint,

• The primary key uniquely identifying a row in a table.
• It doesn’t accept duplicate and null values.
• It can be created on the table level or column level.
• The table-level primary key is also called a composite primary key.
• There can be only one primary key in a table, that can be applied to one column or a group of columns.
• Generally, the primary key constraint is created on the column having a NUMBER data type.

Example of Primary key constraint on column level

SQL> CREATE TABLE master(
       id NUMBER(10) PRIMARY KEY,
       name VARCHAR2(10)
     );
Table created.

The master table having id and name columns. The id column contains primary key constraint so it won’t accept null values and duplicate values.

SQL> INSERT INTO master VALUES(1, 'xyz');
SQL> INSERT INTO master VALUES(2, 'xyz');
SQL> SELECT * FROM master;
        ID NAME
---------- ----------
         1 xyz
         2 xyz

SQL> INSERT INTO master VALUES(NULL, 'xyz');
ORA-01400: cannot insert NULL into ("SCOTT"."master"."ID")

SQL> INSERT INTO master VALUES(1, 'xyz');
ORA-00001: unique constraint (SCOTT.SYS_C0014355) violated

Since there can be only one primary key in a table, so for column level there will be only one column that contains primary key constraints. If we try to use primary key constraint on different columns then we get an error:- ORA-02260: table can have only one primary key

SQL> create table master1(
       id number(10) primary key,
       name varchar2(10) primary key
     );
name varchar2(10) primary key
                    *
ERROR at line 3:
ORA-02260: table can have only one primary key

Composite Primary key in Oracle

The table level primary key is also called as composite primary key. It is a combination of columns as a single primary key. Now, we can apply primary key on more than one column in a table.

• The combination of applied columns values must be unique.
• If at least one column value is different from all previous data then it will allow inserting the data.
• It is not allowed to insert null for any columns having the composite primary key.

SQL> create table compositetest (
       id NUMBER(10),
       name VARCHAR2(15),
       addrs VARCHAR2(15),
       PRIMARY KEY(id, name)
     );
Table created.

SQL> INSERT INTO compositetest VALUES(1, 'abc', 'pqr');
SQL> INSERT INTO compositetest VALUES(2, 'abc', 'pqr');
SQL> INSERT INTO compositetest VALUES(2, 'xyz', 'pqr');
SQL> INSERT INTO compositetest VALUES(1, 'xyz', 'pqr');
SQL> SELECT * FROM compositetest;
        ID NAME            ADDRS
---------- --------------- ---------------
         1 abc             pqr
         2 abc             pqr
         2 xyz             pqr
         1 xyz             pqr

SQL> INSERT INTO compositetest VALUES(1, 'xyz', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014356) violated

SQL> INSERT INTO compositetest VALUES(NULL, 'xyz', 'pqr');
ORA-01400: cannot insert NULL into ("SCOTT"."COMPOSITETEST"."ID")

SQL> INSERT INTO compositetest VALUES(NULL, NULL, 'pqr');
ORA-01400: cannot insert NULL into ("SCOTT"."COMPOSITETEST"."ID")

Foreign key Constraint in Oracle

In all relational databases if we want to establish relationship between tables then we must use referencial integrity constraint foreign key. The properties of foreign key constraint are,

• One table foreign key must belong to another table primary key (or unique) constraint.
• Both primary key and foreign key must belong to the same data type.
• Foreign key values are based on referenced primary key values.
• The primary key doesn’t accept duplicate, null values but the foreign key accepts duplicate and null values.
• It can be created on column level and table level but table level foreign keys are rarely used in real-time projects.
• The table has a primary key is also called a master table and a table having a foreign key is also called a child table.

Column level (references) foreign key,

In column level REFERENCES clause is used to create the foreign key constraint on a column.

Syntax:-
columnName datatype(size) REFERENCES mastertableName ([primaryKeyColumnName])

While creating a column, if we want to apply a foreign key on some particular column, and if the column name is the same as the primaryKeyColumnName then the column name is not required. But for the different column names, it is recommended to use primaryKeyColumnName.

In the above examples, We have already created a table “master” which contains primary key, we will use it here.

SQL> CREATE TABLE child (
       id NUMBER(10) REFERENCES master,
       name VARCHAR2(10)
     );
Table created.

The foreign key is applied to the id column of the child table. The primary key constraint is already applied to the id column of the master table. In both tables, the primary key column and foreign key have the same name.

The id column in child table, will accept only those values which exist in the id column of master table. But it can accept null values and duplicate values.

SQL> SELECT * FROM master;
        ID NAME
---------- ----------
         1 xyz
         2 xyz

SQL> INSERT INTO child VALUES(1, 'pqr');
SQL> INSERT INTO child VALUES(1, 'xyz');
SQL> INSERT INTO child VALUES(1, 'pqr');
SQL> INSERT INTO child VALUES(NULL, 'pqr');
SQL> SELECT * FROM child;
        ID NAME
---------- ----------
         1 pqr
         1 xyz
         1 pqr
           pqr

SQL> INSERT INTO child VALUES(3, 'pqr');
ORA-02291: integrity constraint (SCOTT.SYS_C0014359) violated 
- parent key not found

The id=3 is not available in the master so we can’t insert those value in child table. If we want to insert in the child table then first we have to insert it in the master table then only we can insert them in child table.

Insertion and Deletion From Master Table

Whenever we are establishing relationship between tables by using foreign key constraint then oracle server violates following two rules:-

• Deletion from master table
• Insertion in the child table

In Oracle when we try to delete records from the master table, and if those records are available in the child table then the oracle server returns an error:- ORA-02292: integrity constraint violated – child record found

SQL> SELECT * FROM master;
        ID NAME
---------- ----------
         1 xyz
         2 xyz

SQL> SELECT * FROM child;
        ID NAME
---------- ----------
         1 pqr
         1 xyz
         1 pqr
           pqr

SQL> DELETE FROM master WHERE id=2;
1 row deleted.

SQL> DELETE FROM master WHERE id=1;
ORA-02292: integrity constraint (SCOTT.SYS_C0014359) violated
 - child record found

The master table have id values 1, 2 and child table have id value 1. Since, id=2 doesn’t exist in child table so we can delete it from master table, but id=1 exist in child table that’s why we can’t delete it from master table. To overcome this problem, if we want to delete master table record’s then we have two options,

• Manually delete all related records in child table and then delete record from master table
• Use ON DELETE CASCADE or ON DELETE SET NULL clause with foreign key.

In Oracle when we try to insert other than primary key into foreign key then the oracle server returns an error:- ORA-02291: integrity constraint violated – parent key not found, because by default in all relational databases foreign key values are based on primary key values only. Solution:- we must insert those values into the master table then we can insert them into the child table.

ON DELETE [CASCADE / SET NULL] Clause

Foreign key constraint supports 2 clauses to delete from the records.
1) ON DELETE CASCADE clause
2) ON DELETE SET NULL Clause

ON DELETE CASCADE clause is an optional clause used along with foreign key constraints. When a child table having this clause and if we are deleting master table records then automatically that master table records and also those child table records are deleted. (i.e. both tables data will be deleted).

Oracle also support ON DELETE SET NULL along with foreign key constraint. When a child table having this clause, and if we are deleting primary key values from the master table then automatically that primary key value deleted from master table, and also then foreign key values are set to null within child table.

Syntax:-

columnName datatype(size) 
REFERENCES mastertablename([primaryKeyColumnNames])
ON DELETE [CASCADE / SET NULL];

TRUNCATE TABLE ….. CASCADE Clause

Oracle 12c introduced TRUNCATE TABLE ….. CASCADE clause.

Generally in Oracle, we can’t truncate master table. To overcome this problem Oracle 12c introduced CASCADE clause along with TRUNCATE command, which is used to truncate the master table .

Condition:- The child table must have ON DELETE CASCADE clause.

Syntax:-
TRUNCATE TABLE tablename CASCADE;

Example:-

SQL> CREATE TABLE master1 (
       sno NUMBER(10) PRIMARY KEY
     );
SQL> INSERT INTO master1 VALUES(1);
SQL> INSERT INTO master1 VALUES(2);
SQL> INSERT INTO master1 VALUES(3);
SQL> SELECT * FROM master1;
       SNO
----------
         1
         2
         3

SQL> CREATE TABLE child1 (
       sno NUMBER(10) 
       REFERENCES master 
       ON DELETE CASCADE
     );
SQL> INSERT INTO child1 VALUES(1);
SQL> INSERT INTO child1 VALUES(2);
SQL> SELECT * FROM child1;
       SNO
----------
         1
         2

SQL> TRUNCATE TABLE master CASCADE;
Table truncated. 

SQL> SELECT * FROM master1;
No data found

SQL> SELECT * FROM child1;
No data found

CHECK Constraint In Oracle

Check constraint is used to define logical conditions based on client business rules.

Note:- In Oracle the check constraint doesn’t work with sysdate function.

Column level

Syntax:-
CREATE TABLE tablename(
col1 datatype(size) CHECK(logical conditions),
……
);

In column level, check constraint contains logics condition depends only on a particular column.

Example:-

SQL> CREATE TABLE test(
       name VARCHAR2(20) CHECK(name=UPPER(name))
     );
Table created.

In this table, the “name” column of test table will accept only uppercase character.

SQL> INSERT INTO test VALUES('KNOWPROGRAM');
1 row created.

SQL> INSERT INTO test VALUES('KnowProgram');
ORA-02290: check constraint (SCOTT.SYS_C0014361) violated

SQL> INSERT INTO test VALUES('knowprogram');
ORA-02290: check constraint (SCOTT.SYS_C0014361) violated

SQL> SELECT * FROM test;
NAME
--------------------
KNOWPROGRAM

Table level,

Syntax:-
CREATE TABLE tablename (
col1 datatype(size),
col2 datatype(size),
…..,
CHECK(condition1 and condition2)
);

Example:-

SQL> CREATE TABLE test1 (
       name VARCHAR2(10),
       sal NUMBER(10),
       CHECK(name=UPPER(name) AND sal>5000)
     );
Table created.

SQL> INSERT INTO test1 VALUES('ABC', 6000);
1 row created.

SQL> INSERT INTO test1 VALUES('abc', 6000);
ORA-02290: check constraint (SCOTT.SYS_C0014362) violated

SQL> INSERT INTO test1 VALUES('ABC', 1000);
ORA-02290: check constraint (SCOTT.SYS_C0014362) violated

SQL> INSERT INTO test1 VALUES('abc', 1000);
ORA-02290: check constraint (SCOTT.SYS_C0014362) violated

SQL> SELECT * FROM test1;
NAME              SAL
---------- ----------
ABC              6000

ALTER Constraint in Oracle

Whenever we are copying a table from another table then except NOT NULL constraint, all other constraints are never copied. We have to manually enable the constraint on the existing column of the table.

We can enable constraint on an existing columns of a table or we can add a new column with constraints in the existing table.

In oracle if you want to enable constraints on existing column of a table then we must use table level syntax method.

Syntax to enable constraint on existing column:-

ALTER TABLE tableName
ADD constraintType(column1, column2);

Condition to enable constraint:- Those column must not contain any value which violates those constraint properties.

Note:- If we want to enable constraint without validating and disturbing the existing values of the column then we have to use NOVALIDATE, but it only works with NOT NULL, and CHECK constraints.

Syntax with NOVALIDATE:-

ALTER TABLE tablename 
MODIFY columname ConstraintType NOVALIDATE;

In all relational databases If you want to add a new column along with constraint then we are using column level syntax method.

Syntax to add new column in a table with constraint:-

ALTER TABLE tableName
ADD columnName datatype(size) constraintType;

Enable / Disable / Drop Constraint

We can also provide user-defined name to the constraint and We can also enable / disable / drop the constraint.

Syntax to provide user-defined name to constriant:-

CONSTRAINT user-definedName constriantType

Syntax to rename the constraint:-

ALTER TABLE tableName
RENAME CONSTRAINT oldConstraintName TO newConstraintName;

Note:- constraint name and constraint type are not same. For example:- UNIQUE is constraint type, not a constraint name.

Based on the constraint-name we can disable, enable or drop the constraint. Syntax to disable, enable or drop the constraint,

ALTER TABLE tablename
[DISABLE / ENABLE / DROP] CONSTRAINT constraint-name;

Since there can be only one primary key in a table, so we can also disable, enable or drop the primary key constraint using the below syntax,

ALTER TABLE tablename
[DISABLE / ENABLE / DROP] PRIMARY KEY;

All these points are discussed in details with examples in the particular constraint topic.

Learn more:-

• NOT NULL constraint in Oracle
• UNIQUE constraint in Oracle
• Primary Key constraint in Oracle
• Foreign Key constraint in Oracle
• Check constraint in Oracle

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search