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.
