On Aug 5, 2014, at 12:23 PM, Mario Žic <[email protected]> wrote:
> Thank you Michael for the quick reply, it set me on the right track! I was > looking for some "magic" flag I've missed all this time hh > > Regarding your advice of abandoning the natural PKs altogether (if I > understand correctly), how would one maintain all the "transitional > dependencies" > that would arrise in this way?! when you have surrogate PKs, you never emit an UPDATE for the actual value of a PK, so you never have to deal with any ON UPDATE CASCADE types of scenarios. > This is maybe not so critical in the example I gave, but in reality my tables > have composite PKs and different parts of those are shared between the > tables. It seems to me that without referential integrity this quickly > becomes difficult to maintain. But I am just a beginner and maybe I miss the > big picture - I am curious about your view on this, and those other > experienced users. > > * Before somebody asks why I use sqlite in the first place (since I insist on > the referential integrity), my answer would be ease of deployment - I would > like it to be platform and sysadmin independent :) > > Thanks again !!! > > On Tuesday, August 5, 2014 5:04:47 AM UTC+2, Michael Bayer wrote: > propagation of two levels like that isn’t intrinsic to SQLAlchemy’s > capabilities. For cascading primary key -> foreign key changes in that way, > you’d need to use ON UPDATE CASCADE on your foreign keys, only supported by > some target backends. There are possibly ways to get event listeners at the > SQLAlchemy level to do this also. > > However, in the bigger picture, it probably is not a good idea to use > non-surrogate (e.g. meaningful, which therefore must accommodate changes in > value) primary keys on a database that does not support ON UPDATE CASCADE > functionality. The databases’s behavior is much more comprehensive in this > case. > > > > On Aug 4, 2014, at 1:33 PM, Mario Žic <[email protected]> wrote: > >> I have also tried adding onupdate="cascade" to all foreign keys. (I have >> also disabled passive updates to avoid conflict during update. I think that >> relationship altered values before sqlite got the chance to do it.) I >> enforced referential integrity using proposed event listener method. This >> worked fine within the DB, the updates propagated nicely, but still didn't >> do the job when PK was altered in Sqlalchemy. I think that understanding of >> this behaviour is out of my league for time being :) >> >> On Monday, August 4, 2014 11:24:52 AM UTC+2, Mario Žic wrote: >> Hi, >> >> I have a problem where three tables are linked via relationships A -> B -> >> C, and all the relationships refer to the same property i.e. the primary key >> (PK) of table A is a foreign key (FK) in the table B, and the same FK (table >> B) is a FK in the table C. The example is artificial since I am trying to >> simplify the schema. However, when I modify the PK of A only B sees the >> change after flush. I would expect table C to get updated as well. >> >> The problem and the code is already posted here. >> >> I have a feeling this should be sth simple and documented but I had no luck >> so far. >> >> Thanks for the help! >> >> >> * I tested the code using SQLAlchemy 0.7.7 and 0.9.7; the DB is SQLite >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
