No subselects in constraint (bug?)

On Fri, 13 Jul 2001, Alexey V. Neyman wrote:

Hello there!

[Please Cc: me in followups.]

I tried the following:

CREATE TABLE a (
int4 id
);
CREATE TABLE b (
int4 id
CHECK (id = ANY(SELECT a.id FROM a))
);

Tables are created ok, checking with '\d table' confirms it. But when I
try to insert into table b, e.g.:
INSERT INTO b (id)
VALUES (0);
I get:
ERROR: ExecEvalExpr: unknown expression type 108
Of course, the tuple is not inserted.

As quick dig of code showed, type 108 is T_SubLink which is created for
ANY() subselect, and ExecEvalExpr() function does not handle this type of
node. Is it intentional or a bug?

It's unimplemented, and really should fail at create time (I'm not
sure if it does in 7.1). IIRC, it's only required at FULL SQL92 level
(intermediate level has a no subqueries limitation). The reason is
that the constraint you are making as part of b also constrains
table a and it's not entirely trivial to support complicated subquery
constraints within the current system.

As a workaround for now, you'll probably have to use triggers on a and
b to do the check. (before insert trigger on b and a delete/update
trigger on a).

"Alexey V. Neyman" <avn@any.ru> writes:

CREATE TABLE b (
int4 id
CHECK (id = ANY(SELECT a.id FROM a))
);

ERROR: ExecEvalExpr: unknown expression type 108

More recent versions say

ERROR: Cannot use subselect in CHECK clause

It seems to me that what you really want here is a foreign key
reference, anyway, not a handmade CHECK constraint.

regards, tom lane