Is it a bad idea to have a composite primary key that contains a foreign key?

Question

I am designing a database and have the two following tables:

• t_model (with fields: model_id (PK), model_name)
• t_model_version (with fields: model_id (PK, FK), model_version (PK), start_validity_date, end_validity_date)

As one can see, t_model_version its PK is a composite PK. One of the fields part of the PK is also a FK (the PK of t_model). I was wondering whether this is good or bad practice? I would like to avoid getting into difficulties later on that I cannot yet foresee...

t_model holds different models (e.g. different models/functional forms to predict macroeconomic growth). t_model_version holds the version numbers per model (e.g. a different version of a model means that the functional form is kept but that the coefficient estimates have been updated).

Edit: My question is not about how to add a FK when the field of interest is already part of a composite PK, but whether this a good practice. Hence, my question is not a duplication of Composite Primary Key + Foreign Key.

Answer 1

Just declare them where you find them so the DBMS can enforce them--unless already implied by other declarations--except you must declare a PRIMARY KEY or UNIQUE NOT NULL column list that is referenced by a FOREIGN KEY.

Declare a NOT NULL when a column can't be NULL. Declare a PRIMARY KEY or UNIQUE NOT NULL when subrow values are unique and not null (and don't contain a smaller such subrow, which would imply that). Declare remaining UNIQUE subrows (that don't contain smaller ones, which would imply that). Declare a FOREIGN KEY when subrow values must appear elsewhere as PRIMARY KEY or UNIQUE NOT NULL values (and a chain of FOREIGN KEYs doesn't already say so). Then if necessary declare remaining subrows referenced by FOREIGN KEYs.

PS There's no use worrying about every combination of notion that you learn. Read about information modeling and relational databases to learn good design.

Answer 2

The biggest problem is if the FK field could also be null in your data because it could cause uniqueness issues. However, given your field structure, I would find it unlikely that you would allow nulls in that field in any event.

Answer 3

That's fine--common, even--if you are using natural keys. It's unnecessary if you're using surrogate keys.