Lets say I have a table with these columns:
id | name | foo_id | bar_id | foobar_id
---------------------------------------
I want to make a constraint so at least one of the columns "foo_id", "bar_id" or "foobar_id" must be set.. However only one of these three must be set
Is that possible with an SQL constraint?
"name" (and any other possible columns) must be unaffected of the constraint
The problem is that your database is not properly designed. When a database isn't designed properly these are the kinds of problems that pop up. Here's how I would approach the 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, -- Always = 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, -- Always = 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, -- Always = 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, the Child table should be named something meaningful, not just "Child".
This enforces that My_Table can only have a single child_id and that it has to have at least one - in other words, exactly one.
By including the child_type as part of the primary key for the Child table and using it as part of the foreign key for all of the sub tables, you can enforce that each ID within the Child table only exists once in each sub table.
I primarily use MS SQL Server, so my apologies if some of this syntax isn't quite right for MySQL, but the idea is the important part and all of the pieces are possible in MySQL - it supports PKs, FKs, and unique constraints.
