Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] Candidate key v. primary key

Expand Messages
  • Ronald Bourret
    ... A candidate key is any set of columns that uniquely identifies a row in a table. A primary key is the specific set of columns that has actually been chosen
    Message 1 of 2 , May 3, 2000
    • 0 Attachment
      Phil Friedman wrote:

      >BTW, is there some distinction between "candidate key" and "primary
      >key" or are they two terms for the same thing?

      A candidate key is any set of columns that uniquely identifies a row in a
      table. A primary key is the specific set of columns that has actually been
      chosen by the database designer to uniquely identify rows in the table.
      Thus, all primary keys are candidate keys but not all candidate keys are
      primary keys.

      For example, suppose that in the Employee table, both SocialSecurityNumber
      and EmployeeNumber uniquely identified rows (employees). Both of these are
      candidate keys, but only one would be chosen in the CREATE TABLE statement
      as the primary key.

      For better or worse, I chose to use the term "CandidateKey" in the mapping
      language so people wouldn't feel they were constrained to using only the
      primary key should another candidate key be available.

      -- Ron Bourret
      ________________________________________________________________________
      Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
    • Phil Friedman
      Ron, Thanks for the explanation. In my experience if the key is joined to the foreign key, it s the primary key. But you are right, there are often other
      Message 2 of 2 , May 3, 2000
      • 0 Attachment
        Ron,

        Thanks for the explanation. In my experience if the key is joined to
        the foreign key, it's the primary key.

        But you are right, there are often other columns that uniquely identify
        a row. In my experience there is usually a contrived integer primary
        key and an alternate key of columns with application data. Each unique
        combination of values in the alternate key columns is assigned a new
        primary key value.

        This bears on the update-or-insert request by Carl. Often we want to do
        what I've described above, so update-or-insert would need to check for
        existing values using the alternate key columns but insert a new value
        with the contrived primary key.

        I'm not sure adding an update-or-insert without anticipating variations
        is a good idea. We handle this in stored procedures.

        --
        Regards, Philip Friedman -- Terralink Software Systems -- 207-772-6500 x101

        On Wed, 03 May 2000 14:09:59 CEST, "Ronald Bourret" <rpbourret@...> may have written:

        |Phil Friedman wrote:
        |
        |>BTW, is there some distinction between "candidate key" and "primary
        |>key" or are they two terms for the same thing?
        |
        |A candidate key is any set of columns that uniquely identifies a row in a
        |table. A primary key is the specific set of columns that has actually been
        |chosen by the database designer to uniquely identify rows in the table.
        |Thus, all primary keys are candidate keys but not all candidate keys are
        |primary keys.
        |
        |For example, suppose that in the Employee table, both SocialSecurityNumber
        |and EmployeeNumber uniquely identified rows (employees). Both of these are
        |candidate keys, but only one would be chosen in the CREATE TABLE statement
        |as the primary key.
        |
        |For better or worse, I chose to use the term "CandidateKey" in the mapping
        |language so people wouldn't feel they were constrained to using only the
        |primary key should another candidate key be available.
        |
        |-- Ron Bourret
        |________________________________________________________________________
        |Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
        |
        |
        |------------------------------------------------------------------------
        |You have a voice mail message waiting for you at iHello.com:
        |http://click.egroups.com/1/3555/0/_/735786/_/957355800/
        |------------------------------------------------------------------------
        |
        |To Post a message, send it to: xml-dbms@...
        |
        |To Unsubscribe, send a blank message to: xml-dbms-unsubscribe@...
      Your message has been successfully submitted and would be delivered to recipients shortly.