dropping tables that are referenced in foreign key constraints on tables

[phrr...@googlemail.com]

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)

[Michael Bayer]

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.

[phrr...@googlemail.com]

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.

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.
>

[Michael Bayer]

phrr...@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".


>

[phrr...@googlemail.com]

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:

[Michael Bayer]

phrr...@googlemail.com wrote:
>
> 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?

the tests in engine/reflection.py should do a basic workup of that feature.