MySQL add unique constraint on columns from multiple tables?

Question

Here's an example for what I am looking to do :

I have 3 tables :

Villages (id, name)
Households (id, village_id)
Persons (first_name, last_name, household_id)

I would like to make sure there isn't 2 persons who have the same first name and last name in the same village.

Basically I would like to do something of that sort :

ALTER TABLE persons 
 ADD UNIQUE (first_name, last_name, households.village_id 
            WHERE household_id=households.id)

Is there a way to do something like that ?

Simon at The Access Group · Accepted Answer · 2013-03-27 12:18:11Z

2

You can't really do that directly, but if this is to store real-world data it is theoretically possible for two people with identical name to live within a village, so this may not be the best idea?

If you insist on going down this route then I recommend using a STORED PROCEDURE to handle inserts to this table taking in arguments such as user name and village. The stored procedure could then do a SELECT prior to INSERT to check for conflicts. This would take the place of any INSERT queries. UPDATEs would also need handling via procedure to prevent clashes.

answered Mar 27, 2013 at 12:18

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vic Seedoubleyew Over a year ago

Hi Simon ! Thank you for your answer. The reason why it seems to me more strategic to go this way, is because for our company it seems to be more worth it to handle exceptions when it happens that several persons have the same name in one village, then to take the risk of users creating duplicates and messing up with the data. Your solution sounds good, I'll look into it in more details. Thank you !

Wintermute · Accepted Answer · 2013-03-27 12:27:04Z

2

I think there is no simple way to do that.

You might wanna try creating triggers on before insert/update and throw error on your custom condition. But it is highly dependant on your MySQL version: prior to version 5.5, I believe there is no correct way to raise an error - usually people use hacks like calling non-existent procedures. But if you are on version >5.5 you can use SIGNAL statement: http://dev.mysql.com/doc/refman/5.5/en/signal.html

answered Mar 27, 2013 at 12:27

Wintermute

1,52118 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL add unique constraint on columns from multiple tables?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related