26

I have a MySQL table defined:

File
--------------------------
ID int(11) PK
name varchar(100)
customerId int(11) FK
isPrimaryImage tinyint(1)
....


I've read here that MySQL doesn't support a UNIQUE CONSTRAINT with a WHERE condition. For each customerId we'd have only one primaryImage = 1.

So what else can I do to enforce this constraint?

informatik01
  • 16,038
  • 10
  • 74
  • 104
kasdega
  • 18,396
  • 12
  • 45
  • 89
  • See: http://stackoverflow.com/questions/2504007/mysql-unique-constraint-on-multiple-fields – George Johnston Jul 13 '11 at 12:50
  • Even if MySQL supports unique constraints, you cannot use one here (assuming you'll several secondary images per customer), since you'll have several tuples with the same customerID and isPrimaryImage = 0 (but you could use NULL instead of 0 for these). – Frank Schmitt Jul 13 '11 at 12:52
  • Table design is off, you can't enforce what you want with such layout. – Michael J.V. Jul 13 '11 at 12:53
  • I agree with Michael J.V. - instead of doing it your way, I'd get rid of the isPrimaryImage column and add a customerPrimaryImage table instead (having a CustomerID, a ImageID and a unique constraint on CustomerID). – Frank Schmitt Jul 13 '11 at 12:56

1 Answers1

55

MySQL perfectly supports unique constraints.

It does not support partial constraints/indexes, though, so you would need to mark non-primary images with a NULL instead of 0.

ALTER TABLE file ADD CONSTRAINT ux_file_customer_primary 
UNIQUE (customerId, isPrimaryImage)

You can insert arbitrary number of NULL values into isPrimaryImage but only one non-null value per customer.

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Quassnoi
  • 413,100
  • 91
  • 616
  • 614