Preventing duplicate rows when using UUIDs for primary key

Question

I've got a database table in DB2 (v9.5) like the following:

CREATE TABLE license(key CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY, 
                     name VARCHAR(32) not null,
                     startDate TIMESTAMP not null, 
                     data XML);

(I'm using UUIDs for the primary key as shown in this guide)

My question is one of preventing duplicate entries. My duplicate criteria are as follows:

Duplicate column values are ok. e.g. The name value "Fred" can appear more than once in the table
Duplicate rows (ignoring the key) are not ok. i.e. Two rows cannot have identical values for name AND startDate AND data.

The way I see it I have a few options:

Use something I've missed in the DB2 configuration to make sure that combinations of column values (i.e. name AND startDate AND data) must be unique within the table
I have simply made a poor choice of primary key. Should I instead hash the value of the combined fields into a SHA-1 hash (is this a good/bad choice?) and then use that as the primary key. If so, can I just do a String concatenation of the fields into one long String and use BouncyCastle to do the heavy-lifting for me?
Prior to doing an INSERT, search the table for the row I'm about to insert and don't insert it if it exists already. I don't know about the efficiency of this (or even how to do it - my DB2 XML knowledge is a bit lacking at the moment - but that's perhaps worth a separate question).
Something else?

boes · Accepted Answer · 2011-07-22T14:21:22.567

1

You can add a unique constraint. Columns can be part of a unique constraint in DB2 only if they are not nullable. Another problem here is the data column of type XML. A column of type XML cannot be part of a unique constraint.

You can add a unique constraint for columns name and startdate like this:

CREATE TABLE license(
    key CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY,
    name VARCHAR(32) not null,
    startDate TIMESTAMP not null,
    data XML,
    unique(name, startDate)
);

You can add a part of the XML column to the unique constraint. I cannot give an example here, as it depends on the specific content of your xml data. Search for "GENERATE KEY USING xmlpattern" for information on this subject.

edited Jul 22 '11 at 14:21

answered Jul 22 '11 at 14:09

boes

2,835
2
23
28

A unique contraint can be made on columns that are nullable; but you can only have 1 row with a NULL. Only a primary key constraint requires NOT NULL. – Ian Bjorhovde Jul 22 '11 at 18:22
@Ian Bjorhovde: Again I do not agree with you (see http://stackoverflow.com/questions/3828147/using-prepared-statement-without-row-number-and-over-functions-in-db2/3829806#comment-4070510 ) I tested this on DB2 v9.7 on Linux. If column is nullable DB2 responds with: cannot be a column of a primary key or unique key because it can contain null values.. SQLCODE=-542, SQLSTATE=42831. What DB2 version and OS are you using? – boes Jul 22 '11 at 18:39
In case it helps, the columns will always be not null (i.e. I'll always have data) – Catchwa Jul 22 '11 at 22:28
@Frans: You can create a unique index on a nullable column, and insert a single NULL value into this column. Attempting to insert an additional NULL will result in error. However, you are right, if you actually add a unique constraint the column must be not null. – Ian Bjorhovde Jul 23 '11 at 17:41

Preventing duplicate rows when using UUIDs for primary key

1 Answers1