Thread: Deferred foreign key constraint downsides

I recently had cause to use a deferred foreign key constraint for the
first time. I like it. It seems it could make life simpler, especially
when an obstinate ORM insists on doing things in the wrong order.

The only downside I can see is it may be harder to track down where a
violation occurred since the error won't be raised until commit.

Are there any other downsides to just setting all my foreign keys to
initially deferred?

Thanks.

--
Jack Christensen
jackc@hylesanderson.edu

Jack Christensen <jackc@hylesanderson.edu> writes:

> I recently had cause to use a deferred foreign key constraint for the
> first time. I like it. It seems it could make life simpler, especially
> when an obstinate ORM insists on doing things in the wrong order.
>
> The only downside I can see is it may be harder to track down where a
> violation occurred since the error won't be raised until commit.
>
> Are there any other downsides to just setting all my foreign keys to
> initially deferred?

I'd say, use the feature only as needed.  Gratuitous deviation from
reasonable default should be avoided.

Why promote being able to insert rows in related tables using other than
top-down sequencing?

Sure, if you have an existing app that does that, which you can't
change, go with deferred validation, otherwise no.

YMMV

> Thanks.
>
> --
> Jack Christensen
> jackc@hylesanderson.edu

--
Jerry Sievers
e: gsievers19@comcast.net
p: 305.321.1144

On Fri, 2011-04-08 at 14:08 -0500, Jack Christensen wrote:
> Are there any other downsides to just setting all my foreign keys to
> initially deferred?

It may consume memory resources until the transaction is complete.

Also, when it's possible to write the SQL in an order that always
maintains the integrity of the FK, then it's usually more readable and
understandable. So using immediate constraints may encourage a more
readable style.

Regards,
    Jeff Davis