Can a primary key be used on a foreign key?

Question

The following example may occur?

| Id | nome | data | qnt |

PK -> Id
FK -> (Id, qnt)

Or is there some kind of restriction?

Answer 1

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

Note that all PK, by definition, is a single key.

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

2) A key is a field that identifies the record, as a name of a person. A single key is a field that uniquely identifies a record, such as a CPF.

3) Any key may be composed of more than one column. That depends a lot on schema but in general a key is something that identifies the record.

4) A field that will be used as key should never be changeable. 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 by this?

5) A field quantity is not a good candidate to be used as key, it is a (relatively) mutable field and does not identify the record. Actually, it’s just an attribute, so don’t use it as a key. If you do a lot of research filtering through this field it might be interesting to index it to improve performance but not elevate it to a key.

Edit

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

MSSQL and Mysql.

and the reference to the concept of FK in Wikipedia

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

There may even be some need somewhere to create an FK for a key not unique but the few times I saw such a thing were in serious modeling errors.

Answer 2

The Id, which is primary key, not only can it but is often used in Fks.

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

qnt besides being a bad name to indicate something (I’ll imagine that is quantity) probably is not a good choice to be part of a FK. But I don’t know, there may be a reason. I don’t follow 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. A quantity does not identify anything.

• See the wikipedia article (in English better).
• About composite key.

Answer 3

Yes , an example :

Nota_fiscal

Numero (pk) Valor Date

Nota_fiscal_item

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

Understanding clear that the model is a great simplification.