1) The only restriction for foreign keys is that they should point to a unique key .

Note that every PK, by definition, is a unique key.

Consider the following: the foreign key indicates who the parent record is, the child record depends on and without it would be orphaned. In addition the child record can not have two parents (point!)

2) A key is a field that identifies the record, such as a person's name. A unique key is a field that uniquely identifies a record, such as a CPF.

3) Any key can be made up of more than one column. It will depend a lot on schema but in general a key is something that identifies the registry.

4) A field that will be used as a key should never be mutable . That is: it should never be a field whose value can be changed, for example your CPF, imagine that your CPF could change all the time, how could you be identified for this?

5) A quantity field is not a good candidate to be used as a key, it is a (relatively) changeable field and does not identify the record. Actually it is only an attribute so do not use it as a key. If you do a lot of filtering through this field it might be worth indexing it to improve performance but not raising it to a key.

Edit

@Maniero The implementation of constraint will depend on the DBMS, examples:

MSSQL and MySQL .

and the reference on the concept of FK in Wikipedia

But even though DBMS allows this, it is a breakdown of relational integrity. Because? Well if you change the parent record id, without changing the child records they will be orphaned. If a child can have "n parents" then it is better to reverse the hierarchy and make the "ex-parent" record reference the "ex-child" record. If the relation is N to M then it would be better to create a relationship table with FKs for "parents" and "children."

There may even be a need somewhere to create an FK for a not unique key but the few times I've seen such a thing have been in serious modeling errors.

The Id , which is the primary key, not only can as frequently used in FKs.

If you want to know if the key can be composed, ie use more than one column as FK? Yes, it can and is also common to happen.

qnt besides being a bad name to indicate something (I'll imagine it to be quantity) probably is not a good choice to be part of an FK. But I do not know, there may be a reason. I do not follow the golden rules, I see the need. Normally columns that have not very stable data are not recommended for use in FK. It is preferable to use columns that identify something. An amount does not identify anything.

• See the article on Wikipedia ( in English better ).
• About compound key .

Yes, an example:

Fiscal_note

Number (pk) Value Date

Nota_fiscal_item

Number (pk) (fk nota_fiscal.numero) Num_item (pk) Desc_item Value

Understanding of course the model is a great simplification.