mysql CHECK constraint on multiple columns and values

Question

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

.

updated ur question with a similar fiddle hope it helps you

jmvcollaborator
– jmvcollaborator

2022-08-23 19:37:39 +00:00
Commented Aug 23, 2022 at 19:37 — jmvcollaborator
– jmvcollaborator, Commented Aug 23, 2022 at 19:37

jmvcollaborator · Accepted Answer · 2022-08-23 19:36:49Z

2

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

edited Aug 23, 2022 at 19:36

answered Aug 23, 2022 at 19:30

jmvcollaborator

2,5411 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ergest Basha Over a year ago

dbfiddle.uk/… adaptable to the OP question.

babboon Over a year ago

Code: alter table cc_fluentform_entry_details add constraint calliduniq UNIQUE (field_value, (CASE WHEN field_name = 'call_id' THEN 'call_id' END)); I am getting: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(CASE WHEN field_name = 'call_id' THEN 'call_id' END))' at line 1

jmvcollaborator Over a year ago

Mind to create a fiddle and provide the link ?

babboon Over a year ago

Fiddle works. Mysql does not. Could it be restriction by wordpress?

Collectives™ on Stack Overflow

mysql CHECK constraint on multiple columns and values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related