Wednesday, November 01, 2006

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.

Wow!

That is all I can say and not have content blocked.

The company decided to sell off a small portion to interested buyers. Seems easy enough, every thing is stored in orginisational units, we can shave off that portion, provide it in a easy to load format for the purchasers and we are ready to go, only 5 systems need to have data transferred.. The team of managers and "in the know" people put together to analyse what what was needed said "1 week to develop a plan, another week to extract the data, 1 week of quality control", 3 weeks of work and then all done. We were steaming along steadily well into week two with no major problems, when we started to get calls to our help desk, "Systems is hung", "I can't get logged in".

The help desk guys ran through their normal list of things to check, well, they tried to. Our knowledge base was down, that triggered pretty much every help desk person to pick up the phone and call somebody. Most of the non help desk IT people were in a meeting about the sale and didn't notice anything. It was very funny though, almost like you see in those movies where all of the stars are in a room and all of their cell phones start going off at once to say they had better do something before the world as they know it comes to an end. I was up at the front showing some interesting data I had found in the financials system that was not orginised properly but needed to be extracted when my blackberry started ringing. The IT managers blackberry was vibrating away on the table, and the two SA's cell phones beeping.

After only a few seconds, we all started to file out of the meeting room in a straight line for the server room. I was about 10 feet from the server room at the back of the line, when, wait for it. The fire alarm goes off, the main building fire alarm is whooping and ringing. The hallway to the server room has one of those red fire alarm bells in it. I don't think there was a dry pair of pants in that hallway when that bell went off, those things are freaking loud and the hallway has a door at one end from the lobby and the server room door on the other end, and nothing but concrete, linoleum and at this particular point in time 8 highly trained IT folks to absorb the sound. Despite the fire alarm going off, our manager had been opening the door to the server room and we continue into the server room with the screaming ringing racket of the fire bell going off about 8 feet behind us, as we all came into the server room we found out where the fire that triggered the fire alarm was- in our server room. There was a thick layer of smoke along the roof of the server room and the back of our our tape array rack was billowing smoke, the smell was almost enough to knock one out. Well, being the highly trained IT proffesionals we are, we were stepping on one another trying to get back out of the server room in a mess of arms and legs as 8 of us fit through the standard sized door pretty much at once.

After a very undignified exit into the lobby scaring the people in the lobby pretty much out of their wits we composed ourselves a bit, realized we were still alive and were moving quickly out of the building when one of the SA's spoke up and said "What about the fire suppression system in the room?". My answer, in a moment of pure brilliance at stating the obvious was "It isn't working". We loitered around outside waiting for the billowing smoke to consume the entire building, after what seemed like an hour the fire department showed up. It was really only 7 minutes from the time the alarm went off to the time the FD pulled up. The IT manager and the building manager spoke with the fireman in charge and explained where the fire was.

Us IT folks stood outside with the rest of the building population and waited and wondered. The two SA's were bickering back and forth on who's day it was to send the offsite tapes offsite and wondering if our DR site was in good enough shape to run the company while this place was rebuilt. After another 10 minutes or so the firemen came out and said it was just mostly smoke and they had put the fire out and after a few minutes we can go in and inspect what was up. The building manager would only let one SA and the IT manager into the room for insurance reasons. They didn't touch anything until they had taken about 3,000 pictures and the insurance company over the phone said we could do what was needed to get our business running again.

The Fire inspector figured out what the problem was, it was pretty obvious once you could see it. A power bar that comes built into the rack had ignited into a slow smoldering burn, causing all 8 power cords plugged into it to start to smolder and put off smoke too. It didn't aparranty get hot enough to trigger the fire suppression system in the room. It got plenty hot enough to melt all of the plastic off of all of the power cords, damage the rack and a fibre network hub thing too and generate vast quantities of smoke. All told, under $6,000 dollars damage. Not including the rooms new paint job, contractors to clean the room and the IT departments time to inspect all of the equipment since running it in a smokey environment is apparently bad for it.

The downtime was the remaining portion of the day it happened, and the entire following day but we were up and running at full capacity by 6am on day 3. One of the most junior help desk people we have, a great guy, summed up the entire thing into two words "Mother F***er!" when he was told what was going on. I will let you fill in the bleeped out section.

I promise to post some documentation on primary and unique keys I had been working on later this week. I also have a document on the pitfalls I have ran into using CURSOR_SHARING of SIMILAR or FORCE, but that is a week or two away.