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