Allow a referencing column to reference only one referenced column in relationships

[ayush3797]

Context:

When one column from a referencing table references two columns belonging to the same or different tables, it poses certain problems in dealing with nested insertions. Since this is a bad design practice, we are going to support nested mutation when a referencing column references only one referenced column from any other table.

What needs to be done?

Ensure that one column in an entity/table is referencing only one column in another entity/table.

To illustrate more on the limitation

1. Relationship defined in the database:

This is the case when an FK constraint exist in the database. MsSql allows creating foreign key constraints where one column from a referencing table can be used in multiple Foreign key constraints i.e. one column in the referencing column can reference multiple columns in one or multiple referenced tables. Some simple sql to explain this:

Case 1: One column in referencing table references two different columns in ONE referenced table.

Consider these two tables A (referenced table) and B (referencing table) with 2 FK constraints defined:

CREATE TABLE A(
    categoryid int NOT NULL,
    pieceid int NOT NULL,
       anothercol int unique,
    PRIMARY KEY(categoryid,pieceid)
);

CREATE TABLE B(
    categoryid int NOT NULL,
    pieceid int NOT NULL,
    PRIMARY KEY(categoryid, pieceid)
);

ALTER TABLE B
ADD CONSTRAINT B_A_fk
FOREIGN KEY (categoryid, pieceid)
REFERENCES A (categoryid, pieceid)
ON DELETE CASCADE;

ALTER TABLE B
ADD CONSTRAINT B_A_fk2
FOREIGN KEY (categoryid)
REFERENCES A (anothercol)
ON DELETE NO ACTION;

Now B.categoryid is referencing two columns categoryid, anothercol from table A. While performing a nested insertion, the value a referencing column (to perform insertion on the referencing table) will come via insertion in the referenced table. However, for this case, where the value of a column B.categoryid can assume value of A.categoryid and A.anothercol does not seem logical. The foreign key constraints might be placed for some arbitrary real life use case and might still work fine if you’re using sql via say SSMS, but for nested insertion, this does not make logical sense because using nested insertion means the user is expecting a relation between the tables and such constraints will lead to confusions.

Case 2: One column in referencing table references two different columns in the DIFFERENT referenced table.

Consider the 3 tables D (referenced), E (referencing),F (referenced) with 2 FK constraints defined:

CREATE TABLE D(
    id int IDENTITY(5001, 1) PRIMARY KEY,
    name varchar(max) NOT NULL
);

CREATE TABLE E(
    id int IDENTITY(5001, 1) PRIMARY KEY,
    title varchar(max) NOT NULL,
    publisher_id int NOT NULL
);

CREATE TABLE F(
    id int IDENTITY(5001, 1) PRIMARY KEY,
    [name] varchar(max) NOT NULL,
    current_club_id int NOT NULL,
    new_club_id int NOT NULL
);

ALTER TABLE E
ADD CONSTRAINT E_D_fk
FOREIGN KEY (publisher_id)
REFERENCES D (id)
ON DELETE CASCADE;

ALTER TABLE E
ADD CONSTRAINT E_F_fk
FOREIGN KEY (publisher_id)
REFERENCES F(id)
ON DELETE CASCADE;

E.publisher_id is referencing D.id and F.id. All of these 3 fields are autogenerated. Now suppose I want to perform a nested insertion on these 3 tables together. Since E is the referencing table, the value of the column E.publisher_id has to come via insertion in the referenced table. Now there are two referenced tables: D,F.

1. In which referenced table do we perform the insertion first? D or F?
2. Suppose we chose D and got the value of D.id as X. Now since D,E,F are related, we need to use the same value X for F.id and E.publisher_id. After performing insertion in D, we need to perform insertion in F. But F.id is an autogenerated field, so we cannot provide a value X to it unless we set identity insert on. Do we want to switch on IDENTITY INSERT ourselves?

Both of these edge case arise because of one basic thing:
One column in a referencing table holding FK references to multiple columns in one/multiple referenced tables, and this is what we will not support in the initial version of nested insertions.

2. Relationship defined in the config:

This is the case when a relationship is defined between source and target entities in the config file and no FK constraint exists in the database. In such a case, any entity could be classified as the referencing/referenced entity (Why? See here: #2018). All the columns from the source/target entities can act as referencing columns. Hence, any column involved in a custom relationship is not allowed to be a part of any other relationship defined either in the database or in the config.

[ayush3797]

Will add details on what should be our approach to handle this via code/documentation. Need to run through a simulation of this use case.

[ayush3797]

This will be covered in : #2057.