Cam Jackson Cam Jackson - 3 months ago 6
Python Question

Complex foreign key constraint in SQLAlchemy

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

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 TEMP TABLE systemvariables (
  variable_id integer PRIMARY KEY
, variable    text
, choice_id   integer
);

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

CREATE TEMP TABLE variableoptions (
  option_id integer PRIMARY KEY
, option text
, variable_id integer REFERENCES systemvariables(variable_id)
                      ON UPDATE CASCADE ON DELETE CASCADE
, 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);

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".

Voilá. Exactly what you wanted.


All key columns NOT NULL

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

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 TEMP TABLE systemvariables (
  variable_id integer PRIMARY KEY
, variable    text
, choice_id   integer NOT NULL
);

CREATE TEMP TABLE variableoptions (
  option_id   integer PRIMARY KEY
, option      text
, variable_id integer NOT NULL
     REFERENCES systemvariables(variable_id)
     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 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 9.1 because constraints are enforce after each statement:

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

Details:

Strangely enough, the same thing works in PostgreSQL 8.4, while the documentation claims the same behavior. Looks like a bug in the old version - even if it seems to be beneficial rather than harmful at a first glance. Must have been fixed for newer versions.