Then I must have a bug in the FK introspection. Which unit tests would you suggest getting running first? Is there one that specifically tests foreign key stuff?
pjjH On Mar 13, 3:41 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > phrrn...@googlemail.com wrote: > > > Hi Mike, > > the situation I am encountering is when the other table is not part of > > the metadata collection i.e. SQLAlchemy doesn't know anything about > > it. It looks like the unit-tests enumerate the tables by calling > > table_names() which causes has_table() and reflecttable() to be called > > in turn. There doesn't appear to be a way of expressing that a table > > is referenced by a foreign key constraint .. from what I can see, the > > various dialects implementation of reflecttable check to see if this > > table references other tables. > > I'm not familiar with any test that relies upon that method - every unit > test within SQLA deals with a single MetaData object which contains all > tables, and the foreign key references between those tables are known. > > There is an option called "--dropfirst" which does do the table_names() > thing you mention, but that option is only a convenience measure to rerun > the tests on a database that still has tables leftover from a previously > failed run. It also makes usage of foreign keys to drop tables in the > correct order. > > Any foreign key constraint is represented in a Table object using the > ForeignKey() or ForeignKeyConstraint() object. When tables are loaded via > reflecttable(), the tables are all loaded into a single MetaData object, > and the foreign keys are reflected into ForeignKey objects, and the drop > in order of dependency works in all cases. So I don't know what you mean > by "there doesn't appear to be a way of expressing that a table is > referenced by a foreign key constraint". > > > > > At the moment, I am hacking up my own visit_tables() in the > > SchemaDropper and putting in an explicit check to see if I am targeted > > by any FKs .. if so, I will do an ALTER TABLE on the other tables to > > remove the FK constraint to me. > > > pjjH > > > On Mar 13, 2:41 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > >> SQLAlchemy normally drops tables in order of foreign key dependency so > >> that there's no need for ALTER. in the case that two tables have a > >> mutual > >> foreign key dependency, one of the ForeignKey objects has the > >> "use_alter=True" flag set so that just the one FK gets dropped first via > >> ALTER. > > >> phrrn...@googlemail.com wrote: > > >> > I am encountering a problem with getting the unit tests to run on > >> > Sybase because of cascades of errors originating from failure to drop > >> > a table that is referenced by a FK constraint in another table. When > >> > attempting to drop the people table, I need the SybaseSQLSchemaDropper > >> > to emit SQL like this which first does an ALTER TABLE to remove FK > >> > constraints from all tables which reference people: > > >> > 344:1> ALTER TABLE managers DROP CONSTRAINT managers_1466289798 > >> > 344:2> go > >> > 345:1> drop table people > >> > 345:2> go > >> > 346:1> > > >> > How should one deal with the situation when the referring tables are > >> > not part of the same metadata collection? > > >> > pjjH > > >> > 341:2> sp__revtable people > >> > 341:3> go > >> > -- Table_DDL > > >> > ---------------------------------------------------------------------- > >> > CREATE TABLE people > >> > ( > >> > person_id int identity NOT NULL, > >> > name varchar(50) NULL, > >> > type varchar(30) NULL > >> > ) > > >> > ----------------------------------------------------------------------------------------------------- > >> > ------------------- > > >> > ALTER TABLE people ADD PRIMARY KEY CLUSTERED ( person_id) > >> > -- FOREIGN REFERENCE > > >> > --------------------------------------- > >> > -- No Indexes found in Current Database > > >> > (return status = 0) > >> > 342:1> sp_helpconstraint people > >> > 342:2> go > >> > name > >> > definition created > >> > ------------------- > >> > ------------------------------------------------------------- > >> > ------------------- > >> > managers_1466289798 managers FOREIGN KEY (person_id) REFERENCES > >> people > >> > (person_id) Mar 4 2009 9:12PM > >> > people_17556708171 PRIMARY KEY INDEX ( person_id) : CLUSTERED, > >> > FOREIGN REFERENCE Mar 4 2009 9:11PM > > >> > Total Number of Referential Constraints: 1 > > >> > Details: > >> > -- Number of references made by this table: 0 > >> > -- Number of references to this table: 1 > >> > -- Number of self references to this table: 0 > > >> > Formula for Calculation: > >> > Total Number of Referential Constraints > >> > = Number of references made by this table > >> > + Number of references made to this table > >> > - Number of self references within this table > > >> > (2 rows affected, return status = 0) > > >> > 343:1> sp__revtable managers > >> > 343:2> go > >> > -- Table_DDL > > >> > ---------------------------------------------------------------------- > >> > CREATE TABLE managers > >> > ( > >> > person_id int identity NOT NULL, > >> > status varchar(30) NULL, > >> > manager_name varchar(50) NULL > >> > ) > > >> > ----------------------------------------------------------------------------------------------------- > >> > ------------------- > > >> > ALTER TABLE managers ADD PRIMARY KEY CLUSTERED ( person_id) > > >> > constraint_desc > > >> > ----------------------------------------------------------------------------------------------------- > >> > ------------------------------------------------------------------------------------------------------------- > >> > --------------------------------------------- > > >> > ALTER TABLE managers ADD CONSTRAINT managers_1466289798 > >> > FOREIGN KEY (person_id) REFERENCES people(person_id) > > >> > --------------------------------------- > >> > -- No Indexes found in Current Database > > >> > (return status = 0) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
