Luis Cazares (2/12/2014)
That's the difference of unique keys and primary keys. Primary keys will idenitfy a row within a table and they're essentially a preferred unique key. Unique keys will enforce uniqueness and a table can have as many as needed.Any
key identifies a row in a table. That's what we mean by a key. Actually any superkey identifies a row in a table. A key is just the minimal set of attributes in any superkey.
For me, the primary key on the marriages table should include both columns (husband & wife) or I might choose a surrogate key if the situation is correct for that.
But the requirement is that both Husband and Wife columns be unique (current, monogamous marriages only are permitted). The composite of Husband and Wife cannot be a primary key if Husband and Wife are both candidate keys because a key has to be irreducible
- i.e. the smallest subset of attributes that form a superkey. It's true that in SQL Server you could create a PRIMARY KEY constraint on (Husband,Wife) while still having UNIQUE constraints on (Husband) and (Wife) individually but that's just a syntax peculiarity of SQL. The constraint named "PRIMARY KEY" in that case would not be a primary key constraint at all, it would just become a (mostly redundant) superkey constraint.
Adding a surrogate doesn't really alter things. That just adds a third
key to the picture without answering the essential dilemma of whether and why one key needs to be preferred over any other.