Ensure combination of two columns existing in another table

user_4381271 0
Viewed 204

How could one ensure the combination of two columns existing in another table?

CREATE TABLE a (
    aid          INTEGER      PRIMARY KEY
);

CREATE TABLE b (
    aid          INTEGER      REFERENCES    a(aid),
    bid          INTEGER,
    PRIMARY KEY  (aid, bid)
);

CREATE TABLE c (
    aid          INTEGER      REFERENCES    a(aid),
    cid          INTEGER,
    PRIMARY KEY  (aid, cid)
);

-- combination of (aid, bid) must come from b
-- combination of (aid, cid) must come from c
CREATE TABLE d (
    aid          INTEGER      REFERENCES    a(aid),
    bid          INTEGER      REFERENCES    b(bid),
    cid          INTEGER      REFERENCES    c(bid),
    PRIMARY KEY  (aid, bid)
);

INSERT INTO a values(1);
INSERT INTO a values(2);
INSERT INTO b values(1, 1);
INSERT INTO b values(2, 2);
INSERT INTO c values(1, 1);
INSERT INTO c values(2, 2);
INSERT INTO d values(1, 2, 2);

Obviously the above "CREATE TABLE d" coding failed to ensure

  • combination of (aid, bid) must come from b
  • combination of (aid, cid) must come from c

Thanks.

sql
3 Answers

In addition to Sticky bit and Vladimir Baranov's answers about using FOREIGN KEY and REFERENCES, for sqlite3 users:

"Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection." That means, the user has to open a database first, then run command "PRAGMA foreign_keys = ON;".

The pragma will not work if "the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined)".

user_43812710

You cannot define multicolumn foreign key constraints inline. Define them on their own.

CREATE TABLE d
             (aid integer,
              bid integer,
              cid integer,
              PRIMARY KEY (aid,
                           bid),
              FOREIGN KEY (aid,
                           bid) REFERENCES b (aid,
                                              bid),
              FOREIGN KEY (aid,
                           cid) REFERENCES c (aid,
                                              cid));
user_96614240

You didn't specify what RDBMS you are using. In SQL Server you can create a foreign key using more than one column. The key should point to columns that are in a unique or primary key and you do have the required primary keys in tables B and C.

The definitions of tables A, B and C can remain as they are in the question.

The definition of table D would look like this in SQL Server syntax. Here the foreign key constraints are created using separate ALTER TABLE statements. Different DBMS may use somewhat different syntax.

CREATE TABLE [dbo].[d](
    [aid] [int] NULL,
    [bid] [int] NULL,
    [cid] [int] NULL
) 
GO

ALTER TABLE [dbo].[d]  WITH CHECK ADD  CONSTRAINT [FK_d_b] FOREIGN KEY([aid], [bid])
REFERENCES [dbo].[b] ([aid], [bid])
GO

ALTER TABLE [dbo].[d] CHECK CONSTRAINT [FK_d_b]
GO

ALTER TABLE [dbo].[d]  WITH CHECK ADD  CONSTRAINT [FK_d_c] FOREIGN KEY([aid], [cid])
REFERENCES [dbo].[c] ([aid], [cid])
GO

ALTER TABLE [dbo].[d] CHECK CONSTRAINT [FK_d_c]
GO

It works as expected in my test:

INSERT INTO a values(1);
INSERT INTO a values(2);
INSERT INTO b values(1, 1);
INSERT INTO b values(2, 2);
INSERT INTO c values(1, 1);
INSERT INTO c values(2, 2);


(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Now let's try to insert into D:

INSERT INTO d values(1, 2, 2);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_d_b". 
The conflict occurred in database "TestDB", table "dbo.b".
The statement has been terminated.

These inserts go through, as expected:

INSERT INTO d values(1, 1, 1);
INSERT INTO d values(2, 2, 2);

(1 row affected)

(1 row affected)

OR you can use this syntax:

CREATE TABLE d2 (
    aid          INTEGER,
    bid          INTEGER,
    cid          INTEGER,
    FOREIGN KEY (aid,bid) REFERENCES b (aid,bid),
    FOREIGN KEY (aid,cid) REFERENCES c (aid,cid)
);

In this case the foreign keys would get some autogenerated names, but they will work the same:

INSERT INTO d2 values(1, 2, 2);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d2__351DDF8C".
The conflict occurred in database "TestDB", table "dbo.b".
The statement has been terminated.



INSERT INTO d2 values(1, 1, 2);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d2__361203C5".
The conflict occurred in database "TestDB", table "dbo.c".
The statement has been terminated.

These inserts work, as expected:

INSERT INTO d2 values(1, 1, 1);
INSERT INTO d2 values(2, 2, 2);


(1 row affected)

(1 row affected)

OR, you can use this syntax to give the constraints some meaningful names:

CREATE TABLE d3 (
    aid          INTEGER,
    bid          INTEGER,
    cid          INTEGER,
    CONSTRAINT FK_d3_b FOREIGN KEY (aid,bid) REFERENCES b (aid,bid),
    CONSTRAINT FK_d3_c FOREIGN KEY (aid,cid) REFERENCES c (aid,cid)
);
user_41160170
Related