0

Foreign key should necessarily be a candidate key for a table (say table1)? I know that Foreign key references primary key of some other table (say table2). But for the table1, is it necessary that it should be candidate key?

sumit
  • 10,935
  • 24
  • 65
  • 83

2 Answers2

4

By definition a foreign key is required to reference a candidate key in the target table (table2 in your question). A foreign key does not have to be a candidate key in the referencing table or be part of a candidate key in that table.

nvogel
  • 24,981
  • 1
  • 44
  • 82
3

No. You can have a 1:N relationship, the FK requirement just says that the field has to exist in the other table. Whether that field is unique or not, does not matter.

For reference:

  • a candidate key is an alternative to a PK, it can be one field or the combination of fields (as in a concatenated key)

    1. all this establishes is that there is more than one way to uniquely identify a record of the table
    2. a good alternative to an employee_id might be ssn (social security number)

  • a concatenated key is multiple fields that make up the uniqueness of a record, which can either be an alternative to a PK, or together, act as the PK

    1. because RDBMSs follow at least 1NF, all the fields of the table could be used as the concatentated key
      Note: this is a bad choice and only serves as an example

    2. think of an employee_id field as the one PK of the table, but the combination of firstname,lastname, and startdate would probably uniquely identify everyone on your employees table
      Note: this is an example, there would probably be better alternatives to this in practice

vol7ron
  • 40,809
  • 21
  • 119
  • 172
  • Should FK be a subset of Candidate key? – sumit Jul 07 '11 at 18:33
  • It can, but does not need to be - *should* is irrelevant. A foreign key is just a field that exists in another table. – vol7ron Jul 07 '11 at 18:40
  • 1
    @vol7ron: by "concatenated key" I think you mean what is more often called a "composite key". – nvogel Jul 07 '11 at 19:55
  • 3
    -1 You said, "the FK requirement just says that the field has to exist in the other table. Whether that field is unique or not, does not matter." It does matter. A foreign key constraint has to reference a candidate key. You can't set a foreign key constraint to a column that isn't declared either `PRIMARY KEY` or `UNIQUE`. It's possible this is what you meant in the first place; if you edit your answer, I'll consider removing my "-1". (I'm aware that *I* can edit your answer, but I don't want to put words in your mouth.) – Mike Sherrill 'Cat Recall' Jul 08 '11 at 01:28
  • @Catcall: You are both right and wrong. The SQL standard states that it should reference a PK of another table, however popular RDBMS engines (like MySQL) have modified its implementation and allowed it ([see here](http://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index)). Regardless, I meant that the FK field does not need to be unique in the referencing table (the FK field can repeat values across the records). – vol7ron Jul 15 '11 at 21:48
  • 1
    @vol7ron: Cite the SQL standard, please. Also cite any dbms besides MySQL, popular or not, that allows a foreign key reference to something that's not a candidate key. – Mike Sherrill 'Cat Recall' Jul 15 '11 at 22:46
  • @Catcall: That's not necessary, `mysql` is the only named database tag that the op listed, thus it is what is considered in the answer. – vol7ron Jul 15 '11 at 23:24
  • 1
    @vol7ron: The OP didn't claim that a foreign key should reference a PK or that popular RDBMS engines allow a foreign key to reference something besides a candidate key. You made that claim. MySQL's behavior is not relational, and it's not SQL. Even MySQL's documentation says not to use this misfeature. "The handling of foreign key references to nonunique keys [sic] or keys that contain NULL values is not well defined.... You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys." – Mike Sherrill 'Cat Recall' Jul 16 '11 at 02:40
  • I agree, but I didn't make the clain as previously commented `I meant that the FK field does not need to be unique in the referencing table`, which is not how you interpreted what I said. I'll adjust it, but I am a bit involved in this other project. – vol7ron Jul 16 '11 at 02:53