Solving repetitive composite primary keys in composite entity

Question

I have a composite/bridge entity between two different entities:

CARDS:
    CARD_ID; [Primary Key]
    // Other attributes

BRIDGE_ENTITY:
    CARD_ID; [Primary & Foreign Key]
    MACHINE_ID; [Primary & Foreign Key]
    // Other attributes

MACHINES:
    MACHINE_ID; [Primary Key]
    // Other attributes

Any card registered in the system can be used to access many machines, and vice versa. This is a common many-to-many relationship.

I understand that composite keys cannot have repeating values. But, there is a possibility in my database design that the composite keys in the database design will have different values that repeat multiple times. How do I modify the attributes in such a way that both CARD_ID and MACHINE_ID can have the same or different values across multiple rows without causing a problem? Would adding a new primary key towards the bridge entity called LOG_ID (unique ID) be possible?

Not quite sure what you're wanting. From what you've shown above, the bridge table is permitted to have multiple rows with the same card_id and multiple rows with the same machine_id. What it can't have is multiple rows with the same (card_id, machine_id) combination. Now are you saying you want to have multiple rows with the same (card_id, machine_id)? — karmakaze
@karmakaze Yes, you're correct. Is it correct in my understanding that composite keys do not allow for something like that to work? — gusgus
What is the reason for wanting multiple associations between one CARD_ID and one MACHINE_ID? Whatever that reason is, you can put in a third column of the bridge table and also make it part of the primary key so that you can have (CARD_ID, MACHINE_ID, reason) which is unique. — karmakaze

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-12-31T16:56:38

The middle table bridge_entity has no restriction on duplicates. If you want to allow each pair to appear only once, then you want a unique constraint/index (or primary key declaration):

alter table bridge_entity add constraint unq_bridge_entity_card_id_machine_id
    unique (card_id, machine_id);

Then each pair can only appear once -- which is typically what you want. If you want to allow duplicates, simply do not declare such a constraint and duplicates will be allowed.

Solving repetitive composite primary keys in composite entity

1 Answers