Does any one know what would be the sense of adding an UK on the same column as a PK? I don't find a usage for that, not even if the UK/PK is defined on multiple columns.
On DB2 for example, such constraints can't be created on the same column.
I am not facing an actual case, I am just asking... It's been on my mind for a while.
Can you give a practical example? The idea with one of the keys (uk) having at least one common column with the other key (pK) sounds valid, but I can't think of an example.
Where could someone use that?Why would he need it?
The idea with one of the keys (uk) having at least one common column with the other key (pK) sounds valid, but I can't think of an example.
Leaving aside any differences in indexes or other implementation details ...
Ex 1. Suppose you have a table of reservations (daily time slots) for some resources. Reservations for a given resource cannot overlap (in reality you would need additional constraints to prevent that but the two keys are still valid):
CREATE TABLE Reservation
(ResourceID INT NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
PRIMARY KEY (ResourceID, StartDate),
UNIQUE (ResourceID, EndDate));
ResourceID is common to both keys. This example neatly illustrates something else important: that concept of a "primary" key is totally superfluous anyway - ie there's nothing "special" about a primary key. Exactly the same function and meaning is achieved by:
CREATE TABLE t (x INT NOT NULL PRIMARY KEY, z INT NOT NULL);
Suppose you want a referential constraint in another table that references (x,z) - perhaps because you need both columns to enforce a constraint in that other table for example. For no good reason obvious to me, SQL requires that a FOREIGN KEY constraint must reference exactly the column list of a UNIQUE or PRIMARY KEY constraint. So you would have to create another overlapping constraint if you want to reference (x,z) with a FOREIGN KEY:
CREATE TABLE t (x INT NOT NULL PRIMARY KEY, z INT NOT NULL,
Now you can create the referential constraint on (x,z).
Actually there is another way to answer this second example in SQL Server: you can just create a unique index on (x,z). But since a unique index is de facto a unique constraint as well it doesn't make much material difference.
I can see a very special case where you would want a unique key on the same columns as a primary key. That would be where the order of the columns was changed. There would have to be a compelling (performance) reason to do that, though.