Primary And Unique Keys

Unique and Primary Constraints

There was some confusion on if a primary key constraint allowed multiple null values, or if a

unique constraint actually enforced the uniqueness of null values. So, here is the answer to those

questions.

Primary Keys

Let us start with primary key constraints. I will build a few objects to help us out.

SQL> CREATE SEQUENCE SOMETEST;

Sequence created.

SQL>

SQL> CREATE TABLE TEST1

2 (DATACOLUMN NUMBER NOT NULL);

Table created.

SQL> CREATE TABLE TEST2

2 (DATACOLUMN NUMBER PRIMARY KEY);

Table created.

SQL> CREATE TABLE TEST3

2 (DATACOLUMN NUMBER PRIMARY KEY NOT NULL);

Table created.

SQL> CREATE TABLE TEST4

2 (DATACOLUMN NUMBER);

Table created.

To standardize the testing data, here I will create a table and populate it so that we can use as a base

for the upcoming inserts. I will put in 5 rows of data at creation and two rows of null.

SQL> CREATE TABLE SOMEDATA AS SELECT SOMETEST.NEXTVAL DATACOLUMN FROM DUAL CONNECT BY

LEVEL <= 5;

Table created.

SQL> INSERT INTO SOMEDATA (DATACOLUMN) VALUES (NULL);

1 row created.

SQL> INSERT INTO SOMEDATA (DATACOLUMN) VALUES (NULL);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM SOMEDATA;

DATACOLUMN ISNULL

---------- ----------

1 N

2 N

3 N

4 N

5 N

Y

Y

7 rows selected.

You can see there are 7 rows of data, all unique except for two rows of null values.

First we will do a simple insert into from a full query of the SOMEDATA table.

SQL> INSERT INTO TEST1 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST1 SELECT DATACOLUMN FROM SOMEDATA

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST1"."DATACOLUMN")

The above results were pretty much expected, the column is marked as NOT NULL meaning of

course, no null values at all are allowed.

Now to try the same insert into the TEST2 table with the primary key.

SQL> INSERT INTO TEST2 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST2 SELECT DATACOLUMN FROM SOMEDATA

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST2"."DATACOLUMN")

So, a primary key is automatically NOT NULL. So when we created the table TEST3 we simply did

not need to add the NOT NULL parameter to the column. For completeness here is the same insert

carried out on the TEST3 table.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST3"."DATACOLUMN")

No surprises there. The insert failed.

Now for TABLE4 the table was created with no constraints at all. So now if we do the insert, all 7

of the rows will go into the table.

SQL> INSERT INTO TEST4 SELECT DATACOLUMN FROM SOMEDATA;

7 rows created.

SQL> COMMIT;

Commit complete.

If we are now to add a primary key constraint to the table

SQL>

SQL> ALTER TABLE TEST4 ADD CONSTRAINT TEST4PK

2 PRIMARY KEY (

3 DATACOLUMN

4 )

5 /

ERROR at line 3:

ORA-01449: column contains NULL values; cannot alter to NOT NULL

The database automatically tries to put a NOT NULL check constraint on the column, adding the

primary key fails.

If we try to add the primary key with the command option of NOVALIDATE, the table is altered

and the primary key is added.

1 ALTER TABLE TEST4 ADD CONSTRAINT TEST4PK

2 PRIMARY KEY (

3 DATACOLUMN

4* ) NOVALIDATE

SQL> /

Table altered.

After the primary key is on with the NOVALIDATE we still are unable to insert a null value into

TEST4.

SQL> INSERT INTO TEST4 (DATACOLUMN) VALUES (NULL);

INSERT INTO TEST4 (DATACOLUMN) VALUES (NULL)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST4"."DATACOLUMN")

Even though TEST4 contains null values

1* SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST4

SQL> /

DATACOLUMN I

---------- -

1 N

2 N

3 N

4 N

5 N

Y

Y

7 rows selected.

That situation is one you should be aware of. Even though the primary key is in place, there is bad

data in the table that could throw a large wrench into a well running application.

Unique Keys

Now for unique keys, we will create the same base data table as for primary keys and create new

tables with testing unique keys.

SQL> CREATE TABLE TEST1

2 (DATACOLUMN NUMBER);

Table created.

SQL> CREATE TABLE TEST2

2 (DATACOLUMN NUMBER UNIQUE);

Table created.

SQL> CREATE TABLE TEST3

2 (DATACOLUMN NUMBER UNIQUE NOT NULL);

Table created.

Now for the inserts, first start with TEST1, no constraints at all

SQL> INSERT INTO TEST1 SELECT DATACOLUMN FROM SOMEDATA;

7 rows created.

The TEST2 table has a unique constraint so most people will expect the insert to fail.

SQL> INSERT INTO TEST2 SELECT DATACOLUMN FROM SOMEDATA;

7 rows created.

But no, all 7 rows go in. Unique constraints do not count nulls when checking for uniqueness.

Now, TABLE3 has an unique key and has been set as NOT NULL

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST3"."DATACOLUMN")

SQL> COMMIT;

Commit complete.

A quick couple of queries to verify the data

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST1;

DATACOLUMN ISNULL

---------- ----------

1 N

2 N

3 N

4 N

5 N

Y

Y

7 rows selected.

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST2;

DATACOLUMN ISNULL

---------- ----------

1 N

2 N

3 N

4 N

5 N

Y

Y

7 rows selected.

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST3;

no rows selected

Constraint Objects

The objects that are created when a constraint is created are worthwhile mentioning as well. You

know what this means, time for more test objects, I can feel your joy at this prospect from here.

SQL> CREATE TABLE TEST1

2 (DATACOLUMN NUMBER PRIMARY KEY);

Table created.

SQL> desc test1

Name Null? Type

------------------------------------------------------- -------- ---------------

DATACOLUMN NOT NULL NUMBER

Simple table you have seen before with the primary key defined at creation. The system will

automatically make up a name for the constraint and apply it to the table. If you try this on your

own, your constraint and index name will be different. You can create the constraints with

whatever name you choose, check the documentation on how to do that.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST1';

CONSTRAINT_NAME C INDEX_NAME

------------------------------ - ------------------------------

SYS_C006436 P SYS_C006436

You can see, to efficiently check for violations of the constraint, the database also automatically

creates an index on the table. The INDEX_NAME column clearly shows this. A quick query into

the USER_INDEXES view will confirm this.

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST1';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

SYS_C006436 NORMAL UNIQUE

You will notice that the DATACOLUMN is marked as NOT NULL and there is a constraint on the

table saying so. Observe

SQL> CREATE TABLE TEST2

2 (DATACOLUMN NUMBER PRIMARY KEY NOT NULL);

Table created.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST2';

CONSTRAINT_NAME C INDEX_NAME

------------------------------ - ------------------------------

SYS_C006437 C

SYS_C006438 P SYS_C006438

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST2';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

SYS_C006438 NORMAL UNIQUE

SQL> desc test2

Name Null? Type

------------------------------------------------------- -------- ---------------

DATACOLUMN NOT NULL NUMBER

The database created a CHECK constraint called SYS_C006437 in this case on the table. One

reason this is done is to allow you to drop the primary key constraint and still keep the NOT NULL

constraint. A quick run of the DBMS_METADATA.GET_DDL function will show us what the

database did.

SQL> SELECT DBMS_METADATA.get_ddl('CONSTRAINT','SYS_C006437') FROM DUAL;

DBMS_METADATA.GET_DDL('CONSTRAINT','SYS_C006437')

--------------------------------------------------------------------------------

ALTER TABLE "DBTESTING"."TEST2" MODIFY ("DATACOLUMN" NOT NULL ENABLE)

Something to note is if there is already an index on the column and you add a constraint the

database will "hijack" that index instead of creating a new one. You can explicitly create an index

if you so desire.

SQL> CREATE TABLE TEST3

2 (DATACOLUMN NUMBER);

Table created.

SQL>

SQL> CREATE INDEX MYINDEX ON TEST3(DATACOLUMN);

Index created.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3';

no rows selected

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

MYINDEX NORMAL NONUNIQUE

SQL> DESC TEST3;

Name Null? Type

------------------------------------------------------- -------- ---------------

DATACOLUMN NUMBER

No constraints and only the one index. Add a primary constraint to the table.

SQL> ALTER TABLE TEST3 ADD CONSTRAINT TEST3_PK

2 PRIMARY KEY (

3 DATACOLUMN

4 )

5 /

Table altered.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3';

CONSTRAINT_NAME C INDEX_NAME

------------------------------ - ------------------------------

TEST3_PK P MYINDEX

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

MYINDEX NORMAL NONUNIQUE

SQL> DESC TEST3;

Name Null? Type

-------------------------------------------------------- -------- --------

DATACOLUMN NOT NULL NUMBER

You can see the index is marked as NONUNIQUE even though there is an obvious primary key on

the table. The index name of the primary key is MYINDEX the index we created on the table. We

can now attempt to insert some bad data into the table.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST3"."DATACOLUMN")

So the NOT NULL is of course enforced.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA WHERE DATACOLUMN IS NOT NULL;

5 rows created.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA WHERE DATACOLUMN IS NOT NULL;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA WHERE DATACOLUMN IS NOT NULL

*

ERROR at line 1:

ORA-00001: unique constraint (DBTESTING.TEST3_PK) violated

SQL> ROLLBACK;

Rollback complete.

The uniqueness of the primary key is enforced, even though as you can plainly see there is no

unique index on the table. When the constraint is dropped, the index is not dropped as well, but be

careful there is syntax in the drop constraint command to allow the index to be dropped at the same

time.

SQL> ALTER TABLE dbtesting.test3

2 DROP CONSTRAINT test3_pk

3 /

Table altered.

SQL>

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3';

no rows selected

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

MYINDEX NORMAL NONUNIQUE

The constraint is gone, the original index is still there, all is right in the world. Now, some very

thorough person or more importantly a very thorough GUI comes through with that handy right

click drop ability and the results are very disturbing.

SQL> ALTER TABLE TEST3 ADD CONSTRAINT TEST3_PK

2 PRIMARY KEY (

3 DATACOLUMN

4 )

5 /

Table altered.

SQL> ALTER TABLE dbtesting.test3

2 DROP CONSTRAINT test3_pk DROP INDEX

3 /

Table altered.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3'

;

no rows selected

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

no rows selected

Conclusion

Every table should have a primary key it is plain good RDBMS design. Be aware of the fact the

database does give you the ability to bypass constraints but still make sure you use constraints in

your system, database referential integrity is the best, it is the fastest and no outside code can be as

efficient as the built in database code. Use the database, we are paying for it and why reinvent the

wheel when it rolls along so nice.