Re: Foreign keys

On Sun, 10 Sep 2006, Gregory Stark wrote:

> Chris Mair <chrisnospam(at)1006(dot)org> writes:
>
> > What's the purpose of letting you insert 1000 records, then, at the end
> > say: "hah, all is rolled back becauase the 2nd record was invalid".
> > PG justly throws the exception immediately to let you know it's futile
> > inserting 998 more records.
>
> Well there's plenty of cases where people want that and we support it with
> deferred constraints.
>
> However the OP sounds like he wants something else. I think what he wants is
> when he inserts a record and it fails due to foreign key constraints to report
> all the violated constraints, not just the first one found.
>
> I never run into this problem myself because I think of foreign key
> constraints as more akin to C assertions. They're a backstop to make sure the
> application is working correctly. I never write code that expects foreign key
> constraint errors and tries to handle them.
>
> But there's nothing saying that's the only approach. The feature request seems
> pretty reasonable to me. I'm not sure how hard it would be with the ri
> triggers as written. I'm not sure there's anywhere for triggers to store their
> "return values" so I'm unclear this can even be done using triggers.

I think if we were going to do this that all the constraint violations for
unique, not null, check and foreign keys should be handled similarly, so
we'd probably want something more general than just a way for the ri
triggers to do this. I don't have a good idea of the right solution for
that though.