python - Complex foreign key constraint in SQLAlchemy

Question

I have two tables, SystemVariables and VariableOptions. SystemVariables should be self-explanatory, and VariableOptions contains all of the possible choices for all of the variables.

VariableOptions has a foreign key, variable_id, which states which variable it is an option for. SystemVariables has a foreign key, choice_id, which states which option is the currently selected one.

I've gotten around the circular relationship using use_alter on choice_id, and post_update on SystemVariables' choice relationship. However, I would like to add an extra database constraint that will ensure that choice_id is valid (i.e. it's referring to an option that is referring back to it).

The logic I need, assuming that sysVar represents a row in the SystemVariables table, is basically:

VariableOptions[sysVar.choice_id].variable_id == sysVar.id

But I don't know how to construct this kind of constraint using SQL, declarative, or any other method. If necessary I could just validate this at the application level, but I'd like to have it at the database level if possible. I'm using Postgres 9.1.

Is this possible?

Answer 1

You can implement that without dirty tricks. Just extend the foreign key referencing the chosen option to include variable_id in addition to choice_id.

Here is a working demo. Temporary tables, so you can easily play with it:

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY
, choice_id   int
, variable    text
);
   
INSERT INTO systemvariables(variable_id, variable) VALUES
  (1, 'var1')
, (2, 'var2')
, (3, 'var3')
;

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY
, variable_id int REFERENCES systemvariables ON UPDATE CASCADE ON DELETE CASCADE
, option      text
, UNIQUE (option_id, variable_id)  -- needed for the FK
);

ALTER TABLE systemvariables
  ADD CONSTRAINT systemvariables_choice_id_fk
  FOREIGN KEY (choice_id, variable_id) REFERENCES variableoptions(option_id, variable_id);

INSERT INTO variableoptions  VALUES
  (1, 'var1_op1', 1)
, (2, 'var1_op2', 1)
, (3, 'var1_op3', 1)
, (4, 'var2_op1', 2)
, (5, 'var2_op2', 2)
, (6, 'var3_op1', 3)
;

Choosing an associated option is allowed:

UPDATE systemvariables SET choice_id = 2 WHERE variable_id = 1;
UPDATE systemvariables SET choice_id = 5 WHERE variable_id = 2;
UPDATE systemvariables SET choice_id = 6 WHERE variable_id = 3;

But there is no getting out of line:

UPDATE systemvariables SET choice_id = 7 WHERE variable_id = 3;
UPDATE systemvariables SET choice_id = 4 WHERE variable_id = 1;

ERROR:  insert or update on table "systemvariables" violates foreign key constraint "systemvariables_choice_id_fk"
DETAIL: Key (choice_id,variable_id)=(4,1) is not present in table "variableoptions".

Exactly what you wanted.

All key columns NOT NULL

I think I found a better solution in this later answer:

• How to deal with mutually dependent inserts

Addressing the @ypercube's question in the comments, to avoid entries with unknown association make all key columns NOT NULL, including foreign keys.

The circular dependency would normally make that impossible. It's the classical chicken-egg problem: one of both has to be there first to spawn the other. But nature found a way around it, and so did Postgres: deferrable foreign key constraints.

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY
, variable    text
, choice_id   int NOT NULL
);

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY
, option      text
, variable_id int NOT NULL REFERENCES systemvariables
     ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
, UNIQUE (option_id, variable_id) -- needed for the foreign key
);

ALTER TABLE systemvariables
ADD CONSTRAINT systemvariables_choice_id_fk FOREIGN KEY (choice_id, variable_id)
   REFERENCES variableoptions(option_id, variable_id) DEFERRABLE INITIALLY DEFERRED; -- no CASCADING here!

New variables and associated options have to be inserted in the same transaction:

BEGIN;

INSERT INTO systemvariables (variable_id, variable, choice_id)
VALUES
  (1, 'var1', 2)
, (2, 'var2', 5)
, (3, 'var3', 6);

INSERT INTO variableoptions (option_id, option, variable_id)
VALUES
  (1, 'var1_op1', 1)
, (2, 'var1_op2', 1)
, (3, 'var1_op3', 1)
, (4, 'var2_op1', 2)
, (5, 'var2_op2', 2)
, (6, 'var3_op1', 3);

END;

The NOT NULL constraint cannot be deferred, it is enforced immediately. But the foreign key constraint can, because we defined it that way. It is checked at the end of the transaction, which avoids the chicken-egg problem.

In this edited scenario, both foreign keys are deferred. You can enter variables and options in arbitrary sequence.
You can even make it work with plain non-deferrable FK constraint if you enter related entries in both table in one statement using CTEs as detailed in the linked answer.

You may have noticed that the first foreign key constraint has no CASCADE modifier. (It wouldn't make sense to allow changes to variableoptions.variable_id to cascade back.

On the other hand, the second foreign key has a CASCADE modifier and is defined DEFERRABLE nonetheless. This carries some limitations. The manual:

Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable.

NO ACTION is the default.

So, referential integrity checks on INSERT are deferred, but the declared cascading actions on DELETE and UPDATE are not. The following is not permitted in PostgreSQL 9.0 or later because constraints are enforced after each statement:

UPDATE option SET var_id = 4 WHERE var_id = 5;
DELETE FROM var WHERE var_id = 5;

Details:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?