Home > Net >  mysql CHECK constraint on multiple columns and values
mysql CHECK constraint on multiple columns and values

Time:08-24

I tried to find answer but no luck. I need to create constraint on a table A, that when column X value = 'id' and column Y value is already saved. In other words column Y must be unique only when column X='id'. I created function to check if exists and then tried that: "alter table A add constraint C CHECK(functionName(Y)=0)" but got

Error Code: 1901. Function or expression cannot be used in the CHECK clause

.

CodePudding user response:

Wish you had a fiddle, try the constrain below on the create or alter table.

Constraint should be: UNIQUE (Y, (CASE WHEN X = 'idVal' THEN 'idVal' END))

Here is a Fiddle

  • Related