Thread

1. CHECK evaluation error when using more than one table

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-26T15:57:33Z

   Pedro Alves (pmalves@cosmos.inesc.pt) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   CHECK evaluation error when using more than one table
   
   Long Description
   Creating a table with the code shown, everytime I try to insert values I get the same "ERROR: ExecEvalExpr: unknown expression type 108"
   
   Sample Code
   CREATE TABLE ninhada (
       c_id_mae int,
       n_id int,
       c_id_pai int,
       n_dta_nasc date,
       PRIMARY KEY (n_id, c_id_mae), 
       FOREIGN KEY (c_id_mae) REFERENCES caes (c_id)
          ON DELETE CASCADE,
       FOREIGN KEY (c_id_pai) REFERENCES caes (c_id)
      ,
        CHECK (c_id_mae in 
           (select c.c_id from caes c  where caes.c_id=c_id_mae AND c.c_sexo=0) 
        )
   );
   
   No file was uploaded with this report
   
   
   

2. Re: CHECK evaluation error when using more than one table

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-26T18:27:25Z

   pgsql-bugs@postgresql.org writes:
   > CREATE TABLE ninhada (
   >     c_id_mae int,
   >     n_id int,
   >     c_id_pai int,
   >     n_dta_nasc date,
   >     PRIMARY KEY (n_id, c_id_mae), 
   >     FOREIGN KEY (c_id_mae) REFERENCES caes (c_id)
   >        ON DELETE CASCADE,
   >     FOREIGN KEY (c_id_pai) REFERENCES caes (c_id)
   >    ,
   >      CHECK (c_id_mae in 
   >         (select c.c_id from caes c  where caes.c_id=c_id_mae AND c.c_sexo=0) 
   >      )
   > );
   
   Sub-selects aren't supported in CHECK constraints (and 7.1 will give
   an error message to that effect, rather than bombing out at runtime).
   
   The semantics of such a thing aren't very clear anyway: which rows of
   which tables are being constrained?  In the general case we'd have to
   re-execute the check expression for every row of its table after any
   modification to any row of that table or any other one referenced in
   the CHECK clause.  That's not going to be practical.
   
   You could fake it by putting the sub-select in a function, but be aware
   that the function is only going to be called at insert or update of
   a row of ninhada; there isn't any cross-check on updates to caes
   that may invalidate ninhada rows.
   
   			regards, tom lane