Hi Mike, In case you haven't been following the SO question, I've added another answer which is basically Erwin's solution done with SQLAlchemy. Feel free to use all or part of the code I've put up in any additions you make to the SQLA docs. I think people could really benefit from seeing the complete code for such a use case.
Cheers, Cam 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<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, 7 December 2011 1:19 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Complex foreign key constraint 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<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au/> From: sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Jackson, Cameron Sent: Tuesday, 6 December 2011 1:29 PM To: sqlalchemy@googlegroups.com<mailto: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<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au/> From: sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Tuesday, 6 December 2011 1:13 PM To: sqlalchemy@googlegroups.com<mailto: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<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto: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<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto: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<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto: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. ------------------------------------------------------------------------- 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.
