On Dec 5, 2011, at 7:40 PM, Jackson, Cameron wrote: > I have two tables, SystemVariables and VariableOptions. SystemVariables is > self-explanatory, and VariableOptions contains all of the possible values 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 cyclic 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. > 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.
you'd need to create an INSERT/UPDATE trigger that invokes a procedure which will perform the check via SELECT, this is the pseudcode version: assert SELECT inserted.choice_id in (select id from variable_options where variable_id=inserted.id) when you write a trigger you get access to variables like "inserted" and "updated" which indicate the row in question. the mechanics of writing the procedure and raising an exception in PG aren't syntaxes I know off the top of my head, you'd start with the TRIGGER stuff at http://www.postgresql.org/docs/9.0/static/sql-createtrigger.html -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
