Is it necessary that the Foreign key of some table should be the candidate key of the same table?

Foreign key should necessarily be a candidate key for a table (sa开发者_运维技巧y 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?

---

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.

---

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