CODEWITHSUNDEEP
mysqlunique
Mike Daneman
Mike Daneman

Reputation: 11

MySQL unique name for each index in table

I have the following table, where id_A and id_B are primary keys

id_A   id_B  name
------------------
1       1    Bob
1       2    Jerry
2       1    Anna

I want to ensure that "name" is always unique for a given id_A, but I'm allowed to have duplicate names for different values of id_B. So, for example, assuming the table values above

id_A=3, id_B=1, name="Bob" would be legal, but
id_A=2, id_B=2, name="Anna" would not.

Do I need to setup a secondary table to accomplish this? I tried setting up a table which had id_A and name as primary keys and linked it to the original table. However, this prevented me from updating either table individually since it complained that it failed a foreign key constraint.

Thanks.

Upvotes: 0

Views: 43

Answers (1)

Nick
Nick

Reputation: 147196

You can add a UNIQUE key over id_A and name i.e.

ALTER TABLE data ADD UNIQUE (id_A, name);

That will prevent inserting a duplicate name value for a given id_A value.

Demo on dbfiddle

Upvotes: 1

Related Questions