mysql - SQL - Only allow insert in one of three columns (one MUST be set though) -

lets have table these columns:

id | name | foo_id | bar_id | foobar_id --------------------------------------- 

i want make constraint @ least 1 of columns "foo_id", "bar_id" or "foobar_id" must set.. 1 of these 3 must set

is possible sql constraint?

"name" (and other possible columns) must unaffected of constraint

the problem database not designed. when database isn't designed these kinds of problems pop up. here's how approach design of these relationships:

create table child (     child_id    int    not null,     child_type  int    not null,    -- 1 = foo, 2 = bar, 3 = foobar     constraint pk_child primary key clustered (child_id, child_type),     constraint ui_child_childid unique (child_id) )  create table my_table (     id          int            not null,     name        varchar(20)    not null,     child_id    int            not null,     constraint pk_my_table primary key clustered (id),     constraint fk_child_mytable foreign key (child_id, child_type) references child (child_id, child_type) )  create table foo (     child_id        int            not null,     child_type      int            not null,    -- = 1     some_foo_column varchar(20)    not null,     constraint pk_foo primary key clustered (child_id),     constraint fk_foo_child foreign key (child_id, child_type) references child (child_id, child_type) )  create table bar (     child_id        int            not null,     child_type      int            not null,    -- = 2     some_bar_column varchar(20)    not null,     constraint pk_bar primary key clustered (child_id),     constraint fk_bar_child foreign key (child_id, child_type) references child (child_id, child_type) )  create table foo_bar (     child_id        int            not null,     child_type      int            not null,    -- = 3     some_foo_bar_column varchar(20)    not null,     constraint pk_foo_bar primary key clustered (child_id),     constraint fk_foo_bar_child foreign key (child_id, child_type) references child (child_id, child_type) ) 

of course, child table should named meaningful, not "child".

this enforces my_table can have single child_id , has have @ least 1 - in other words, one.

by including child_type part of primary key child table , using part of foreign key of sub tables, can enforce each id within child table exists once in each sub table.

i use ms sql server, apologies if of syntax isn't quite right mysql, idea important part , of pieces possible in mysql - supports pks, fks, , unique constraints.