CODEWITHSUNDEEP
mysqldatabaseforeign-keys
ncf
ncf

Reputation: 556

Duplicate foreign keys in mysql

I have a table like the following:

On table a, I would like to have fields that are foreign keys of b.id and c.id. However, having the same combination of b and c foreign keys should only be allowed once. For instance, if b.id=1 and c.id=1 (in table a foreign keys) it is fine, but there cannot be another record with b.id=1 and c.id=1, etc.

I'm using MySQL, how do I alter my database to enforce this?

Upvotes: 0

Views: 200

Answers (3)

Franz
Franz

Reputation: 675

Try something like:

ALTER TABLE a ADD UNIQUE INDEX myindex (b.id, c.id);

Upvotes: 0

Manoj Sharma
Manoj Sharma

Reputation: 616

try this:

ALTER TABLE `a` ADD UNIQUE `unique_index`(`b.id`, `c.id`);

so, in this combination your value will always be unique and other value will not insert.

Upvotes: 1

Ajinkya Pisal
Ajinkya Pisal

Reputation: 591

you can create unique index on b.id and c.id

ALTER TABLE `a` 
ADD UNIQUE INDEX `unique_b_c` (`b.id`, `c.id`);

Upvotes: 2

Related Questions