I have two tables,
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
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?
You can implement that without dirty tricks. Just extend the foreign key referencing the chosen option to include
variable_id in addition to
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;
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 ACTIONcheck 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
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;
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.
EDIT: The 0.7.4 release of SQLAlchemy (released the same day I started asking about this issue, 7/12/’11!), contains a new
autoincrement value for primary keys that are also part of foreign keys,
ignore_fk. The documentation has also been expanded to include a good example of what I was originally trying to accomplish.
All is now explained well here.
If you want to see the code I came up with before the above release, check the revision history of this answer.
I really do not like circular references. There is usually a way to avoid them. Here is an approach:
SystemVariables --------------- variable_id PRIMARY KEY (variable_id) VariableOptions --------------- option_id variable_id PRIMARY KEY (option_id) UNIQUE KEY (variable_id, option_id) FOREIGN KEY (variable_id) REFERENCES SystemVariables(variable_id) CurrentOptions -------------- variable_id option_id PRIMARY KEY (variable_id) FOREIGN KEY (variable_id, option_id) REFERENCES VariableOptions(variable_id, option_id)