How to enforce an "ALL-TO-ALL" relationship?

Question

Suppose I have the following structure (SQL Server syntax):

CREATE TABLE A (
   key_A int NOT NULL PRIMARY KEY CLUSTERED,
   info_A nvarchar(50) NULL
);

CREATE TABLE B(
   key_B int NOT NULL PRIMARY KEY CLUSTERED,
   info_B nvarchar(50) NULL
);

CREATE TABLE C(
   key_C int NOT NULL PRIMARY KEY CLUSTERED,
   key_A int NOT NULL,
   key_B int NOT NULL,
   info1 nvarchar(50) NULL,
   info2 nvarchar(50) NULL,
   info3 nvarchar(50) NULL
);
ALTER TABLE C WITH CHECK ADD  CONSTRAINT FK_C_A FOREIGN KEY(key_A) REFERENCES A (key_A);
ALTER TABLE C WITH CHECK ADD  CONSTRAINT FK_C_B FOREIGN KEY(key_B) REFERENCES B (key_B);

So, table C has two primary keys to table A and table B. Table C has to have the cartesian product of table A and table B. That means all combinations, so when a new record is inserted in table A, we have to insert in table C several rows with the new reference in A by all the rows in B. And viceversa, in the case of insertion in B.

The question is, how can you enforce the integrity of such relationship in SQL Server, in which table C has to have all combinations of A and B? Or, if you consider such a structure a bad practice, what alternative tables do you recommend that do not add the hassle of having to do DISTINCT selects and such?

Thanks!

Link to Fiddle: Fiddle

Answer 1

You need to have inserted into table A / B before you can reference this new entry in table C. The only way I know of would be to create a trigger on tables A and B to populate table C when a new entry was made to either of those tables. The issue there is what do you then put into the other fields? Since these are nullable I assume you're happy defaulting them to null? If not (i.e. you want the user to input valid values) the only way to do this would be in the logic of your application, rather than at database level (or by using stored procedures to populate these tables where those procs had suitable logic to create the appropriate entries in C in addition to A / B).

Trigger Code Example:

use StackOverflowDemos
go
if OBJECT_ID('TRG_C_DELETE','TR') is not null drop trigger TRG_C_DELETE
if OBJECT_ID('TRG_A_INSERT','TR') is not null drop trigger TRG_A_INSERT
if OBJECT_ID('TRG_B_INSERT','TR') is not null drop trigger TRG_B_INSERT
if OBJECT_ID('C','U') is not null drop table C
if OBJECT_ID('A','U') is not null drop table A
if OBJECT_ID('B','U') is not null drop table B
go

CREATE TABLE A 
(
   key_A int NOT NULL IDENTITY(1,1) CONSTRAINT PK_A PRIMARY KEY CLUSTERED,
   info_A nvarchar(50) NULL
);
go

CREATE TABLE B
(
   key_B int NOT NULL IDENTITY(1,1) CONSTRAINT PK_B PRIMARY KEY CLUSTERED,
   info_B nvarchar(50) NULL
);
go

CREATE TABLE C
(
   key_C int NOT NULL IDENTITY(1,1) CONSTRAINT PK_C PRIMARY KEY CLUSTERED,
   key_A int NOT NULL CONSTRAINT FK_C_A FOREIGN KEY(key_A) REFERENCES A (key_A),
   key_B int NOT NULL CONSTRAINT FK_C_B FOREIGN KEY(key_B) REFERENCES B (key_B),
   info1 nvarchar(50) NULL,
   info2 nvarchar(50) NULL,
   info3 nvarchar(50) NULL
);
go

CREATE TRIGGER TRG_A_INSERT 
ON A 
AFTER INSERT
AS
BEGIN

    --add new As to C
    insert C (key_A, key_B)
    select key_A, key_B
    from inserted
    cross join B

END;
go


CREATE TRIGGER TRG_B_INSERT 
ON B 
AFTER INSERT
AS
BEGIN

    --add new As to C
    insert C (key_A, key_B)
    select key_A, key_B
    from inserted
    cross join A

END;
go

CREATE TRIGGER TRG_C_DELETE
ON C 
AFTER DELETE
AS
BEGIN

    DELETE 
    FROM B
    WHERE key_B IN
    (
        SELECT key_B
        FROM DELETED d
        --ths row onwards are here to cover the circumstance that the record being deleted isn't the only instance of B in this table
        WHERE key_B NOT IN 
        (
            SELECT key_B 
            FROM C
            WHERE C.key_C NOT IN
            (
                SELECT key_C 
                FROM deleted 
            )
        )
    )

    DELETE 
    FROM A
    WHERE key_A IN
    (
        SELECT key_A
        FROM DELETED d
        --ths row onwards are here to cover the circumstance that the record being deleted isn't the only instance of A in this table
        WHERE key_A NOT IN 
        (
            SELECT key_A 
            FROM C
            WHERE C.key_C NOT IN
            (
                SELECT key_C 
                FROM deleted 
            )
        )
    )

END;
go
insert A select 'X'
select * from C --no results as no Bs yet
insert A select 'Y'
select * from C --no results as no Bs yet
insert B select '1'
select * from C --2 results; (X,1) and (Y,1)
insert A select 'Z'
select * from C --3 results; the above and (Z,1)
delete from A where info_A = 'Y'
select * from C --3 results; as above since the previous statement should fail due to enforced referential integrity
insert C (key_A, key_B, info1) 
select a.key_A, b.key_B, 'second entry for (Y,1)'
from A
cross join B
where a.info_A = 'Y'
and b.info_B = '1'
select * from C --4 results; as above but with a second (Y,1), this time with data in info1
delete from C where info1 = 'second entry for (Y,1)'
select * from C --3 results; as above but without the new(Y,1)
select * from A --3 results
select * from B --1 result
delete from C where key_A in (select key_A from A where info_A = 'Y')
select * from C --2 results; (X,1) and (Z,1)
select * from A --2 results; X and Z
select * from B --1 result
delete from C where key_B in (select key_B from B where info_B = '1')
select * from C --0 results
select * from A --0 results
select * from B --0 result

SQL Fiddle demo here (NB: only SQL Fiddle only shows the outputs from table C; also the error demo has been commented out since this errors the whole thing rather than just the one error line). http://sqlfiddle.com/#!3/34d2f/4