Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    15

    Unanswered: senseless UK on PK?

    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.


    Thanks!

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Are the two indexes identical? For instance if one index is clustered or one is filtered or has included columns that the other doesn't then that might be a valid reason.

    If it is a multi-column index then is the order of columns the same and the sort order the same?

  3. #3
    Join Date
    Mar 2009
    Posts
    15
    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?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can think of a tenuous example of when you'd do this on a single column: indexed views!
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by alecs
    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):

    Code:
    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:

    Code:
    ... PRIMARY KEY (ResourceID, EndDate),
     UNIQUE (ResourceID, StartDate));
    or:

    Code:
    ... UNIQUE (ResourceID, StartDate),
     UNIQUE (ResourceID, EndDate));
    Ex 2. A different example. Suppose you have:

    Code:
    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:

    Code:
    CREATE TABLE t (x INT NOT NULL PRIMARY KEY, z INT NOT NULL,
     UNIQUE (x,z));
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dportas
    [the] 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:
    In SQL Server the difference is slight, but still worth noting.

    A primary key is a non-null unique index.
    A unique key will allow a single null value.

    <beingPicky />
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  8. #8
    Join Date
    Mar 2009
    Posts
    15
    Thanks a lot guys! That makes a lot of sense.

    Regards,
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •