Unique constraints, NULL, and where SQL got it wrong (imho)

Most databases allow NULL values in a unique constraint. I like this because sometimes you really do need to do that. The problem is that they don’t consider NULL as a unique value in that constraint. That means that you can have multiple rows with the same “values” (I know, NULL is not really a value), but in some uses it is.

I have a table with four columns that need to be maintained as unique, but where some of the columns could be NULL indicating a total lack of value for that column. Due to the proprietary nature I can’t give the exact table details.

The columns are parent_id, metric_id, object_id, item_id

parent_id and metric_id are NOT NULL, they must have a value. object_id and item_id could be NULL indicating a “default” value – in other words if you want to lookup something and you can’t find a match on all four columns, you can search for a partial match where object_id and/or item_id are NULL. Objects and items have foreign keys to their parent tables so they have to have valid values, I can’t just put an empty value in there.

This means I can have

parent_id    metric_id       object_id    item_id
1            10              2123         39392
1            11              NULL         NULL
1            11              NULL         NULL


and that is not valid because I now have two “duplicate” values. I’m trying to think of a reason you’d ever want to allow that type of duplicate.

From what I’ve read SQL Server would not allow this, so kudos to SQL Server.

Feel free to add your thoughts or ideas.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s