Обсуждение: No subselects in constraint (bug?)
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? I use 7.0.3, but 7.1.2 code looks pretty the same in ExecEvalExpr(). The platform is FreeBSD 4.3-R. TIA for responses, Alexey. -- -------------------------------------------------+--------------------------- Yes. We have good news! | Regards, Alexey V. Neyman Well, that is to say, we have no news. | mailto: avn@any.ru ----------------------------------( Pkunk, SC2 )-+---------------------------
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