Tech Off Post

Single Post Permalink

View Thread: Database keys
  • 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.