Re: How to temporarily disable a table's FK constraints?

On 11/5/07, Kynn Jones <kynnjo(at)gmail(dot)com> wrote:
> On 11/5/07, Erik Jones <erik(at)myemma(dot)com> wrote:
>
> > On Nov 5, 2007, at 10:50 AM, Kynn Jones wrote:
> > > Is there a standard way to disable a table foreign-key constraint
> > > temporarily?
> > >
> > > I thought that this would be a fairly common thing to want to do...
>
> > Can you explain what it is you're actually trying to do? As in,
> > what's your use case for needing to do this?
>
> A Perl script that needs to update a referring table with many new
> entries before knowing the foreign keys for each new record. (I
> described a similar situation in a recent post, Subject: Populating
> large DB from Perl script.)
>
> Also, Ron, the *owner* of a table is not a "regular user" as far as
> that table is concern. That user has special privileges, including
> that of dropping constraints. What I seek to do is no greater a
> violation of the idea of enforcing relational integrity than is the
> ability to drop constraints altogether.
>
> BTW, I realize that I can just drop and reinstate constraints, but
> from the point of view of writing a Perl script to do all this, it
> would be much easier if I could just disable temporarily all the FK
> constraints on a table.

But those aren't the same things. If userA has permission to add /
drop FKs, and drops them, inserts data, and then reapplies the foreign
key, userA will get an error if they've managed to dork out the data
(i.e. data with no foreign key).

OTOH, if userA just switches off FK enforcement, adds data and turns
them back on, the data can now be incoherent.

Things get even more interesting if other users are involved.

If one and only one user ever uses the table, and that use is
absolutely sure the data is coherent, then they are the same thing.
Otherwise, they certainly are not.