Benjamin Rasmussen February 2016

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

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

Answers


Tom H February 2016

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 tha

Post Status

Asked in February 2016
Viewed 3,682 times
Voted 7
Answered 1 times

Search




Leave an answer