Oracle - Integrity Constraints - Part I

I always wanted to write an article about integrity constraints in Oracle. Today is the right day to express my thoughts on this. Constraint means "Limitation or Restriction". With Oracle, I'd put it as "Restriction to do something". A database user is prevented from doing an action to a column in table where a restriction is in place. The restriction to perform the action is conveyed to Oracle through some "magic statements" which we will look into shortly. Also, the law maker here, cannot be a law breaker.

From here on, I'll use the Oracle terminology "Constraint". There are five types of constraints in Oracle. We will see three of them in this post with examples. Two other with some more advanced concepts will be covered later.

Not NULL constraint
A column in a table cannot take NULL values. So, if any user tries to insert a NULL value into the column, throw an error telling "Boss, look here! you are not supposed to insert NULL value into this column. So, put proper values".

Enforcing this constraint prevents the column from NULL values. Let us go by an example to understand it much better. So, open up SQLPLUS, create a table like this

CREATE TABLE TEMP_SR (
COL1 NUMBER,
COL2 VARCHAR2(25) NOT NULL,
COL3 VARCHAR2(30)
)

Now the "NOT NULL" for COL2 specifies that COL2 cannot take NULL values. Let us do some inserts to prove it.

INSERT INTO TEMP_SR VALUES ('1','ABC','DEF') -- gets inserted
INSERT INTO TEMP_SR VALUES ('2',NULL,'DEF') -- Fails, COL2 is NULL

The second statement returns an error message to the user.

ORA-01400:cannot insert NULL into ("SCOTT","TEMP_SR"."COL2")

The ways by which we can overcome this not NULL constraint will be covered in a later post of mine.

Unique Constraint

Unique Constraint, as the name goes, makes sure that the values present in a column are always unique. It will not allow duplicate values to be inserted at any point in time. Unique constraint can also be defined on the combination of two columns. In such a case, care is taken by Oracle that the combination is always unique. Consider this example

CREATE TABLE TEMP_SR (
COL1 NUMBER UNIQUE,
COL2 VARCHAR2(25),
COL3 VARCHAR2(15)
)

Here COL1 is unique. So, Oracle will not allow duplicate values into it. Hence, if I execute the following insert statements in the order specified,

INSERT INTO TEMP_SR VALUES ('1','ABC','DEF') -- gets inserted
INSERT INTO TEMP_SR VALUES ('1',NULL,'DEF') --- Fails

the second one fails with the reason

ORA-00001: unique constraint(SCOTT.SYS_C001572884) violated

Note that SCOTT.SYS_C001572884 is the name of the constraint assigned by Oracle. Every constraint is assigned a unique name. (if you don't give one..So name it nicely..)

Now, let us see how to make the combination of two columns as unique => extension of the approach discussed above. I've to modify the CREATE TABLE syntax slightly for this. Here we go,

CREATE TABLE TEMP_SR1 (
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER,
UNIQUE(COL1,COL2)
)

Here I've specified that the combination of COL1 and COL2 is unique. Hence, the insert statement,

INSERT INTO TEMP_SR1 VALUES ('1','1','2') -- gets insertedINSERT INTO TEMP_SR1 VALUES ('1','3','2') -- gets inserted
INSERT INTO TEMP_SR1 VALUES ('1','1','6') --- Fails

A part that is left out is to name the constraint. It is done by using the following syntax during CREATE TABLE.

CREATE TABLE TEMP_SR1 (
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER,
CONSTRAINT ABV UNIQUE(COL1,COL2)
)

Now, when the constraint is violated, Oracle will give an exception like this

ORA-00001: unique constraint(SCOTT.ABV) violated

ain't this neat?

We haven't discussed about dropping constraints, adding constraints to the table that is already existing and more. There are three more types of constraints that needs to be addressed

1. Primary Key Constraint
2. Foreign Key Constraint
3. Check Constraint

These three constraint will be addressed in the next blog on this series. And we will slowly gain momentum, and look at the constraint in much more depth.