CODEWITHSUNDEEP
sqldatabasedatabase-designforeign-keysprimary-key
gusgus
gusgus

Reputation: 13

Solving repetitive composite primary keys in composite entity

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?

Upvotes: 0

Views: 232

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 1

Related Questions