[email protected] 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" <[email protected]> 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. >> >> [email protected] 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
