How to enforce unique constraint on two columns together in mysql?

Question

In my mysql table, I store rows which act as walls on a playing field. The table columns are like:

id
owner_id
x
y

The id column, is the primary key, the owner_id is the foreign key, the x and y are the location of the wall. How can I set a constraint that the x and y columns together be unique? If I put a unique constraint on both columns, then it will act separately on each column. This is because I don't want 2 existing walls in the same location. But walls can be destroyed and built on that location again later.

Anyone know how to do this?

Thanks

Possible duplicate of http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql? — Sean Quinn, Apr 29 '14 at 16:31
Someone with that much rep should really know how to check for duplicates — Patrick Q, Apr 29 '14 at 16:32

score 0 · Accepted Answer · answered Apr 29 '14 at 16:32

0

 alter table yourTableName add unique index(x, y);

This makes the x, y combination unique, and cannot be duplicated just like any index constraint.

answered Apr 29 '14 at 16:32

Marshall Tigerus

3,675
10
37
67

How to enforce unique constraint on two columns together in mysql?

1 Answers1