--- In IB-Architect@y..., Jim Starkey <jas@n...> wrote:

> Circular foreign key dependencies are natural and useful
> (contentious statement #1), but a near disaster from a
> DDL perspective (contentious statement #2) since a table
> can't be dropped with another table referencing it, and
> there is no easy to delete an unnamed foreign key
> constraint. Anybody have any rare and keen insight into
> the problem?

Insight, no; comments, sure.

According to the SQL standard, all constraints have names -- either
explicit names assigned lovingly by the user, or implicit names
generated by the vendor.

Also, according to the SQL standard, the syntax for DROP statements
(DROP CONSTRAINT, DROP TABLE, DROP COLUMN, etc.) includes a <drop
behavior> clause. Drop behavio[u]r can be RESTRICT or CASCADE.

If you choose RESTRICT and there are dependencies, then the DROP
<whatever> statement fails. If you choose CASCADE and there are
dependencies, then the dependencies (views, dependent constraints,
whatever) are cleaned up as a side effect of the drop statement.

For what it's worth.

db

>
> Among the solutions:
>
> 1. alter table blah drop foreign key (seg1, seg2)
> (is a references clause required? optional?)
>
> 2. drop table blah regardless
> (leaves dependency tables kinda in limbo)
>
> 3. drop tables blah, yuckola, payroll
> (get them all at once and who cares?)
>
> 4. Outlaw circular dependencies (naw...)
>
> Any ideas?
>
>
>
> Jim Starkey