yes, I had an intuition that a composite FK could do it as I've seen that approach before, but I couldn't figure it out. we have test coverage for cases exactly like that as well and I think I may even add this to the docs.
On Dec 6, 2011, at 12:17 AM, Jackson, Cameron wrote: > Just to follow up on this again: I asked the question at Stack Overflow as > well, and someone showed me how I can accomplish this in SQL using a compound > foreign key: http://stackoverflow.com/a/8395021/665488 > > Now I just have to convert his SQL into SQLAlchemy declarative syntax and > I'll have a nice clean solution. > > Cameron Jackson > Engineering Intern > Air Operations > Thales Australia > Thales Australia Centre, WTC Northbank Wharf, Concourse Level, > Siddeley Street, Melbourne, VIC 3005, Australia > Tel: +61 3 8630 4591 > cameron.jack...@thalesgroup.com.au | www.thalesgroup.com.au > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On > Behalf Of Jackson, Cameron > Sent: Tuesday, 6 December 2011 1:29 PM > To: sqlalchemy@googlegroups.com > Subject: RE: [sqlalchemy] Complex foreign key constraint > > Thanks Mike. Might put this on the back-burner for now, and I'll look into it > again when I get the time. > > Cheers. > > Cameron Jackson > Engineering Intern > Air Operations > Thales Australia > Thales Australia Centre, WTC Northbank Wharf, Concourse Level, > Siddeley Street, Melbourne, VIC 3005, Australia > Tel: +61 3 8630 4591 > cameron.jack...@thalesgroup.com.au | www.thalesgroup.com.au > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On > Behalf Of Michael Bayer > Sent: Tuesday, 6 December 2011 1:13 PM > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] Complex foreign key constraint > > > 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. > ------------------------------------------------------------------------- > DISCLAIMER: This e-mail transmission and any documents, files and previous > e-mail messages attached to it are private and confidential. They may contain > proprietary or copyright material or information that is subject to legal > professional privilege. They are for the use of the intended recipient only. > Any unauthorised viewing, use, disclosure, copying, alteration, storage or > distribution of, or reliance on, this message is strictly prohibited. No part > may be reproduced, adapted or transmitted without the written permission of > the owner. If you have received this transmission in error, or are not an > authorised recipient, please immediately notify the sender by return email, > delete this message and all copies from your e-mail system, and destroy any > printed copies. Receipt by anyone other than the intended recipient should > not be deemed a waiver of any privilege or protection. Thales Australia does > not warrant or represent that this e-mail or any documents, files and > previous e-mail messages attached are error or virus free. > ------------------------------------------------------------------------- > -- > 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. > ------------------------------------------------------------------------- > DISCLAIMER: This e-mail transmission and any documents, files and previous > e-mail messages attached to it are private and confidential. They may contain > proprietary or copyright material or information that is subject to legal > professional privilege. They are for the use of the intended recipient only. > Any unauthorised viewing, use, disclosure, copying, alteration, storage or > distribution of, or reliance on, this message is strictly prohibited. No part > may be reproduced, adapted or transmitted without the written permission of > the owner. If you have received this transmission in error, or are not an > authorised recipient, please immediately notify the sender by return email, > delete this message and all copies from your e-mail system, and destroy any > printed copies. Receipt by anyone other than the intended recipient should > not be deemed a waiver of any privilege or protection. Thales Australia does > not warrant or represent that this e-mail or any documents, files and > previous e-mail messages attached are error or virus free. > ------------------------------------------------------------------------- > > -- > 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. -- 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.
