Re: Foreign keys

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > 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.
>
> It seems pretty unwieldy to me: it's not hard to imagine a long INSERT
> throwing millions of separate foreign-key errors before it's done, for
> instance. And then there's the cascading-errors problem, ie, bogus
> reports that happen because some prior step failed ... not least being
> your client crashing and failing to tell you anything about what
> happened because it ran out of memory for the error list.
>
> My advice is to rethink the client code that wants such a behavior.

Well you're still talking about the case of multiple queries deferring all
constraint checks to the end of the transaction. I'm not sure what the
original poster had in mind but that's not how I read it and it wasn't what I
was speculating about.

I was only thinking of situations like:

INSERT INTO TABLE USER (name, department, zipcode) VALUES ('Tom', 0, '00000');

We'll fail with an error like "violates foreign key constraint
user_department_fkey". It won't say anything about the zipcode also being
invalid.

I sure hate UIs that give you one error at a time so you have to keep fixing
one problem, clicking ok again, only to have yet another error pop up, rinse,
lather, repeat until you finally get all the problems sorted out.

Now I've never actually run into this because as I mention I always treated
the database constraints as assertion checks independent of the application
which usually enforces stricter conditions anyways. But I could see someone
arguing that having two independent sets of code implementing the same set of
conditions is poor.

In any case the same logic that leads to it being desirable to report all the
errors to the user in a UI and not just report them one by one also applies to
the database. I'm not sure it's the most important issue in the world, but it
does seem like a "it would be nice" feature if it reported all the errors in
the statement, not just the first one it finds.

--
greg