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.

(The only thing below that might be unfamiliar to someone who knows SQL but not sqlite is sqlite’s “WITHOUT ROWID” clause. By default, sqlite adds a hidden integer auto-incrementing “rowid” column on tables. Using the “WITHOUT ROWID” command turns that off.)

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. The hidden “rowid” column seems like a natural choice but these columns cannot be used as a foreign key in sqlite, where foreign keys must map to declared columns. However, there is a workaround, “INTEGER PRIMARY KEY AUTOINCREMENT” is a special alias in sqlite that will cause a named column to refer to “rowid”. So that’s what we will try.

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 (
    id_a INTEGER PRIMARY KEY AUTOINCREMENT,
    name_a TEXT 
);

CREATE TABLE B (
    id_b INTEGER PRIMARY KEY AUTOINCREMENT,
    name_b TEXT
);

CREATE TABLE C (
    id_c INTEGER PRIMARY KEY AUTOINCREMENT,
    name_c TEXT
);

CREATE TABLE AB (
    id_ab INTEGER PRIMARY KEY AUTOINCREMENT,
    id_a INT NOT NULL,
    id_b INT NOT NULL,
    UNIQUE (id_a, id_b)
    FOREIGN KEY (id_a) REFERENCES A(id_a),
    FOREIGN KEY (id_b) REFERENCES B(id_b)
);

CREATE TABLE BC (
    id_bc INTEGER PRIMARY KEY AUTOINCREMENT,
    id_b INT NOT NULL,
    id_c INT NOT NULL,
    UNIQUE (id_b,id_c)
    FOREIGN KEY (id_b) REFERENCES B(id_b),
    FOREIGN KEY (id_c) REFERENCES C(id_c)
);

CREATE TABLE ABC (
    id_abc INTEGER PRIMARY KEY AUTOINCREMENT,
    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_a),
    FOREIGN KEY (id_b) REFERENCES B(id_b),
    FOREIGN KEY (id_c) REFERENCES C(id_c)
    -- this table is under-constrained compared to the compound foreign key version previously given
);

As indicated table ABC is under-constrained. Here’s the same test data to prove it (NULL is used on insert for the auto-increment columns):

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

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

INSERT INTO ABC VALUES (NULL,1,1,3,'should pass');
INSERT INTO ABC VALUES (NULL,1,2,3,'should fail'); -- but does not

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.

Answer

I’m not sure about SQL part, however. Instead of Foreign Key to A, B, and C model, I suggest you foreign key to table AB and BC instead.

so your model is something like

Class ABC(models.Model):
    ....
    ab = ForeignKey(AB, "insert_other_constraint_here")
    bc = ForeignKey(BC, "insert_other_constraint_here")

But the catch here is, everytime you want to create object of ABC you have to get AB and BC first:

ab = AB.objects.get(a=a,b=b)
bc = BC.objects.get(b=b,b=c)
ABC.objects.create(...,ab=ab,bc=bc)

That way, if AB doesn’t have combination of (a,b) or BC doesn’t have combination of (b,c) it will raise an Error.

Edit:

Doing it this way however, make INSERT INTO ABC VALUES (1,2,3,'should fail'); Not doable, since you need AB and BC instead of A,B,C value. If you still want to use values of A,B,C to create ABC:

I guess the other way is to override save() method.

def save(self, *args, **kwargs):
    ab = AB.objects.filter(a=self.a,b=self.b)
    bc = BC.objects.filter(b=self.b,b=self.c)
    if ab is None or bc is None:
         "Raise error here"
    super(ABC, self).save(*args, **kwargs) 

So it checks whether AB and BC objects are present first before creating.