CODEWITHSUNDEEP
mysqlsql
Vic Seedoubleyew
Vic Seedoubleyew

Reputation: 10526

MySQL add unique constraint on columns from multiple tables?

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

I have 3 tables :

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 ?

Upvotes: 1

Views: 280

Answers (2)

Wintermute
Wintermute

Reputation: 1531

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

Upvotes: 2

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

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.

Upvotes: 2

Related Questions