Sql – Does a table with a surrogate key require a unique constraint on a natural key to be in 1NF

databasedatabase-designsql

The pragmatists have won the argument of surrogate vs. natural primary keys in favor of surrogate keys*. In my own work I always use SQL Server identity columns without a second thought. But it occurs to me that, for a table to be in 1st normal form, I should be able to identify a natural key and enforce it with a unique constraint. I can't do this for all the tables in my database, so my database doesn't even meet the lowest criteria of normalization.

Do you agree that a table with a surrogate primary key must also have a unique constraint on a natural key in order to be in 1NF?

*I think Joe Celko is still fighting the good fight, see the last paragraph.

Edited to add:
Thanks for the responses. My impression is that adding a unique constraint is not a common practice, so I'm somewhat surprised that the responses so far have been unanimous.

Best Answer

Yes!

If the table is supposed to record at most instance of the natural key, you need a constraint on the relevant column(s) to enforce that. Otherwise, you can end up with a table of 50,000,000 rows, each with a different ID value but otherwise identical -- which is pathetic or ludicrous or a travesty, depending on your viewpoint.

Related Solutions

Surrogate vs natural key: hard numbers on performance differences

Use both! Natural Keys prevent database corruption (inconsistency might be a better word). When the "right" natural key, (to eliminate duplicate rows) would perform badly because of length, or number of columns involved, for performance purposes, a surrogate key can be added as well to be used as foreign keys in other tables instead of the natural key... But the natural key should remain as an alternate key or unique index to prevent data corruption and enforce database consistency...

Much of the hoohah (in the "debate" on this issue), may be due to what is a false assumption - that you have to use the Primary Key for joins and Foreign Keys in other tables. THIS IS FALSE. You can use ANY key as the target for foreign keys in other tables. It can be the Primary Key, an alternate Key, or any unique index or unique constraint., as long as it is unique in the target relation (table). And as for joins, you can use anything at all for a join condition, it doesn't even have to be a key, or an index, or even unique !! (although if it is not unique you will get multiple rows in the Cartesian product it creates). You can even create a join using non-specific criterion (like >, <, or "like" as the join condition.

Indeed, you can create a join using any valid SQL expression that evaluate to a boolean.

Mysql – Creating Surrogate Key table with other unique constraints

The Device3 and Data3 looks good to me. I wouldn't mess with adding an extra table unless there was some compelling reason to. And likely any compelling reason to create third table boils down to just dealing with the composite foreign key, like in the original Device/Data1 model.

It seems like all that is missing is UNIQUE constraint. You can add a UNIQUE constraint (i.e. UNIQUE INDEX) on the combination of owner and devname columns. e.g.

 CREATE TABLE device3 
 ( id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
 , owner    VARCHAR(30) NOT NULL COMMENT ''
 , devname  VARCHAR(30) NOT NULL COMMENT ''
 , ...

 , UNIQUE KEY device3_UX1 (owner, devname)

 ) ...

Or, if you already have the device3 table created, you can just add add a unique index to the existing table. e.g.

 CREATE UNIQUE INDEX device3_UX1 ON device3 (owner, devname)

Best Answer

Yes!

Related Solutions

Surrogate vs natural key: hard numbers on performance differences

Mysql – Creating Surrogate Key table with other unique constraints

Related Topic