Subject Re: Circular Foreign Key Dependencies
--- 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
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.


> 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