Re: "...integer[] references..." = error

On Sat, 7 Sep 2002, Joel Rodrigues wrote:

> On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote:
>
> > Not really. There are some performance issues and such surrounding
> > it. Arrays are positional and not multisets, so you get some
> > wierdness at the conversion.
> >
> > For example:
> > I have (3,4) in an array. I update it to (4). For foreign
> > key purposes, is this effectively a delete of 3 (ie no
> > check required) or a delete of 4 and a change of 3->4.
> > If the array has 100 elements and I remove the first one,
> > do I do 99 foreign key checks or do I try to determine
> > that's all that happened. What if the rest of the elements
> > were randomly assorted?
>
> Hello Stephan, I understand what you're saying. I didn't see
> that. Thanks. But would there be any problem with allowing
> subselects within CHECK expressions ?

You might want to read past discussions. The big problem is
that check constraints with subselects act as constraints on
the tables mentioned in the subselects as well and we currently
have no good way to manage that automatically.

Imagine a check constraint like:

create table table1 (
a int check ((select max(col1) from table2)>a);
);

What can make that check constraint violated?
Adding a new row to table1 with a>=max(col1),
Updating table1 row to a>=max(col1),
Deleting the max row from table2 such that a>=max(col1)
Updating table2's max row such that a>=max(col1).

The first two are easy, it's the second two that are
wierd. Note that in this case inserting to table2
I don't believe can violate the constraint, but switching
the > to a < would make delete safe and insert possibly
violate.

If you don't care about the second pair of constraints
there, you can hide alot of the functionality in a
trigger or function.