Categories
Mastering Development

How to work around Django’s lack of composite keys in this complicated case?

I can’t seem to figure out how to work around the lack of composite keys in Django for the following case. I’m going to write the schema I’d like using SQLite3 dialect:

PRAGMA foreign_keys = ON;

CREATE TABLE A (
    id_a INT PRIMARY KEY,
    name_a TEXT 
) WITHOUT ROWID;

CREATE TABLE B (
    id_b INT PRIMARY KEY,
    name_b TEXT
) WITHOUT ROWID;

CREATE TABLE C (
    id_c INT PRIMARY KEY,
    name_c TEXT
) WITHOUT ROWID;

CREATE TABLE AB (
    id_a INT,
    id_b INT,
    PRIMARY KEY (id_a, id_b),
    FOREIGN KEY (id_a) REFERENCES A(id_a),
    FOREIGN KEY (id_b) REFERENCES B(id_b)
) WITHOUT ROWID;

CREATE TABLE BC (
    id_b INT,
    id_c INT,
    PRIMARY KEY (id_b, id_c),
    FOREIGN KEY (id_b) REFERENCES B(id_b),
    FOREIGN KEY (id_c) REFERENCES C(id_c)
) WITHOUT ROWID;

CREATE TABLE ABC (
    id_a INT,
    id_b INT,
    id_c INT,
    blah TEXT,
    PRIMARY KEY (id_a, id_b, id_c),
    FOREIGN KEY (id_a, id_b) REFERENCES AB(id_a, id_b),
    FOREIGN KEY (id_b, id_c) REFERENCES BC(id_b, id_c)
) WITHOUT ROWID;

Tables AB and BC have compound key constraints that are easily worked around using surrogate keys but table ABC has complex key constraints and cannot be implemented directly in Django.

Here’s some test data

INSERT INTO A VALUES (1, "a1"), (2, "a2"), (3, "a3");
INSERT INTO B VALUES (1, "b1"), (2, "b2"), (3, "b3");
INSERT INTO C VALUES (1, "c1"), (2, "c2"), (3, "c3");

INSERT INTO AB VALUES (1,1), (1,2), (2,1), (2, 3);
INSERT INTO BC VALUES (1,3), (2,1), (3,1);

-- This should work because (1,1) is in AB and (1,3) is in BC.
INSERT INTO ABC VALUES (1,1,3,'should pass');

-- This should fail because although (1,2) is in AB, (2,3) is not in BC.
-- note that this should fail despite 1,2,3 are unique together
INSERT INTO ABC VALUES (1,2,3,'should fail');

The obvious first steps to try getting it to work Django is to use surrogate keys (particularly easy in SQLITE by removing the "WITHOUT ROWID" command which auto-adds a sequential "id" column). Similar questions on StackOverflow about Django and compound keys mention to use NOT NULL and UNIQUE constraints so we can do that too:

PRAGMA foreign_keys = ON;

CREATE TABLE A (
    name_a TEXT 
);

CREATE TABLE B (
    name_b TEXT
);

CREATE TABLE C (
    name_c TEXT
);

CREATE TABLE AB (
    id_a INT NOT NULL,
    id_b INT NOT NULL,
    UNIQUE (id_a, id_b)
    FOREIGN KEY (id_a) REFERENCES A(id),
    FOREIGN KEY (id_b) REFERENCES B(id)
);

CREATE TABLE BC (
    id_b INT NOT NULL,
    id_c INT NOT NULL,
    UNIQUE (id_b,id_c)
    FOREIGN KEY (id_b) REFERENCES B(id),
    FOREIGN KEY (id_c) REFERENCES C(id)
);

CREATE TABLE ABC (
    id_a INT NOT NULL,
    id_b INT NOT NULL,
    id_c INT NOT NULL,
    blah TEXT,
    UNIQUE (id_a, id_b, id_c)
    FOREIGN KEY (id_a) REFERENCES A(id),
    FOREIGN KEY (id_b) REFERENCES B(id),
    FOREIGN KEY (id_c) REFERENCES C(id)
    -- this table is under-constrained compared to the compound foreign key version previously given
);

As indicated table ABC is under-constrained. Is the only option to use before insert triggers to test for values that would otherwise slip through? Using Django constraints, I’ve found no way to reference tables AB and BC in the constraint checks.

Leave a Reply

Your email address will not be published. Required fields are marked *