Do you know if the alias is required on the table1? etc: CHECK(EXISTS( SELECT * FROM table1 WHERE type = new.type; ));
--- In [email protected], Ann Harrison <aharrison@...> wrote: > > On Thu, Sep 22, 2011 at 8:05 PM, firebirdsql <firebirdsql@...> wrote: > > > I have the following check constraint for table1: > > EXISTS > > ( > > SELECT * FROM table1 AS a WHERE a.type = type; > > ); > > > > The problem with this is that the right side of the assignment operator, > > type, refers to a.type instead of the new record's type. Sometimes it refers > > to the record's type, sometimes it refers to a.type and it depends on the > > table. > > > > To fix it, I have to do WHERE a.type = table1.type. > > > > The alias for values in the new record is new, as in > > EXISTS > ( > SELECT * FROM table1 AS a WHERE a.type = new.type; > ); > > And I hope the "=" is intended to be an equality operator, not an assignment > operator. We're speaking SQL here, not C. > > > Good luck, > > Ann > > > [Non-text portions of this message have been removed] >
