Menu
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?
744
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
A primary key should be unique, but a unique key cannot necessarily be the primary key. Primary key implements entity integrity, whereas the unique key enforces unique data.
The syntax for creating a unique constraint using an ALTER TABLE statement in SQL Server is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
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.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
