Tech Off Thread

12 posts

Database keys

Back to Forum: Tech Off
  • User profile image
    spivonious

    I've been reading a lot about the various database keys recently and have a good grasp on them.

    • Primary key - should be something unique and immutable, no nulls allowed
    • Unique key - should be something unique, but can change and have nulls
    • Foreign key - ties a column/columns in a child table to a parent table's primary/unique key

     

    Makes perfect sense. But one thing that I'm wondering about is why would you ever have more than one unique key? I can't think of any examples where that would be desirable.

    edit: also, allowing nulls in a unique key and then using it in a foreign key is just weird. The child insert succeeds but you effectively orphan your child rows. This is all with Oracle, btw.

  • User profile image
    Matthew van Eerde

    Consider an Employees table with fields:

    EmployeeID <-- primary key
    EmailAddress <-- unique, NULLs allowed
    Office <-- unique, NULLs allowed

    > allowing nulls in a unique key and then using it in a foreign key is just weird. The child insert succeeds but you effectively orphan your child rows

    Not sure what you mean.

  • User profile image
    W3bbo

    , Matthew van Eerde wrote

    Consider an Employees table with fields:

    EmployeeID <-- primary key
    EmailAddress <-- unique, NULLs allowed
    Office <-- unique, NULLs allowed

    > allowing nulls in a unique key and then using it in a foreign key is just weird. The child insert succeeds but you effectively orphan your child rows

    Not sure what you mean.

    In Database classes you often get told to use something in the problem/biz domain as a PK rather than an auto-generated identity, such as an SSN.

    I've never agreed with this, primarily because it's inefficient from a computational PoV (aren't SSN strings?) but also because it isn't always immutable, and not everyone has an SSN, for example.

    Is there any reason why you wouldn't want to use auto-generated identities?

  • User profile image
    cheong

    @W3bbo: If you ever want to migrate your database, or restructure your database and you're using auto-generated identites, you've got to be very careful as it'd be very easy for childs to join to wrong parent.

    I've done a project which requires porting an Access database using autoincrement field as keys to MS SQL server. In the end I have to introduce a temp key column in each such table to hold the old PK, update the childs' key field with aid of this temp field, then delete the temp field after the migration is proved successful.

    And some DBAs got upset if they found the key field of certain table discontinuous after delete... (this is rather unimportant indeed) Tongue Out

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    PerfectPhase

    @cheong: When porting databases like your example, we tend just to use 'set identity insert on' during the insert to ensure the key fields remain the same.  Of course if the keys are of a different type then this isn't going to help.

  • User profile image
    cheong

    @PerfectPhase: Of course. Smiley

    But those were SQL2005 days, when the import/export wizard of Management Studio lacks the interface to enable identity insert for multiple selected table. Although you can enable identity insert for in column mapping view of individual table, clicking 50+ tables to set identity insert this way is pretty painful.

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    Tokter

    @cheong: Hehe, if you don't wanna click your mouse, then you can always type the coresponding SQL:

    SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

  • User profile image
    spivonious

    , Matthew van Eerde wrote

    Consider an Employees table with fields:

    EmployeeID <-- primary key
    EmailAddress <-- unique, NULLs allowed
    Office <-- unique, NULLs allowed

    > allowing nulls in a unique key and then using it in a foreign key is just weird. The child insert succeeds but you effectively orphan your child rows

    Not sure what you mean.

    Let's say you make a child table that uses email/office as the foreign key and you put in a child with nulls for those values. There's no way to get back to the parent table. Except in SQL Server where NULL is treated as a distinct value rather than an unknown, i.e. null = null -> true. Oracle correctly treats it as an unknown so null = null -> null.

     

    Anyway, my main question was when would you ever have multiple unique keys. In your example above, it's multiple parts of one unique key. I can't think of any situation where you'd have two separate unique keys for a table.

     

    @Webbo - I was taught to use parts of the domain as primary keys, but am coming to understand that this isn't always a good idea, especially if one of those values will be changing regularly. I've shifted over to the auto-increment numeric key with the domain key as a unique key.

  • User profile image
    figuerres

    , spivonious wrote

    *snip*

    Let's say you make a child table that uses email/office as the foreign key and you put in a child with nulls for those values. There's no way to get back to the parent table. Except in SQL Server where NULL is treated as a distinct value rather than an unknown, i.e. null = null -> true. Oracle correctly treats it as an unknown so null = null -> null.

     

    Anyway, my main question was when would you ever have multiple unique keys. In your example above, it's multiple parts of one unique key. I can't think of any situation where you'd have two separate unique keys for a table.

     

    @Webbo - I was taught to use parts of the domain as primary keys, but am coming to understand that this isn't always a good idea, especially if one of those values will be changing regularly. I've shifted over to the auto-increment numeric key with the domain key as a unique key.

    Try this:

    say it's a bank, and you have customer checking accounts and ATM / Debit cards.

    so each checking account has a different number and each ATM card has a different number but each is a key to the same customer and account balance.

    2 Keys one primary the other needed to avoid creating duplicate ATM card numbers.

     

  • User profile image
    PerfectPhase

    @figuerres: In your example wouldn't you have three entities Customer, ATM, Checking, the latter two FKed to the Customer, but each only has a single unique index?  That of course assumes that each customer can hold multiple checking and ATM accounts, otherwise you would need multiple unique indexes.

    The a common example I run into is where what would be the domain PK, someone’s SSN or email address for example, is sensitive so you need a surrogate primary key to exchange with third parties; you want to enforce uniqueness in both the primary and the surrogate.

     

     

  • User profile image
    spivonious

    , PerfectPhase wrote

    @figuerres: In your example wouldn't you have three entities Customer, ATM, Checking, the latter two FKed to the Customer, but each only has a single unique index?  That of course assumes that each customer can hold multiple checking and ATM accounts, otherwise you would need multiple unique indexes.

    The a common example I run into is where what would be the domain PK, someone’s SSN or email address for example, is sensitive so you need a surrogate primary key to exchange with third parties; you want to enforce uniqueness in both the primary and the surrogate.

     

     

    I agree. Even with a 1:1 relationship, I'd still have separate tables for ATM and checking accounts, as you're going to have more information than just the account numbers (and stuffing them in one table would violate normalization Smiley ). But you'd still need the unique keys in that situation to give the FK something to latch onto.

    Still, something with more than two independent unique values isn't so impossible. Say an employee table with employee id (PK), phone extension (unique), email address (unique).

  • User profile image
    davewill

    @spivonious: figuerres example is valid.  and your response is as well.  it comes down to a balance.  it isn't always good to be nth normal form.  sometimes for performance denormalization has to be done.  in those cases you can end up with multiple unique keys.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.