Forum OpenACS Q&A: alter table add constraint

Collapse
1: alter table add constraint
Posted by Jonathan Ellis on 11/12/01 01:41 AM
I want to add a field, default_party_id, to the users table, and check to make sure that the referenced party actually belongs to the user. 

bf2=# alter table users add default_party_id int references parties
(party_id);

ALTER

bf2=# alter table users add constraint party_id_valid check
(user_id = (select user_id from parties where party_id = 
default_party_id))
;

bf2-# bf2-# ERROR:  ExecEvalExpr: unknown expression type 108
I'm not sure what to make of this error...
Collapse
2: Response to alter table add constraint (response to 1)
Posted by Don Baccus on 11/12/01 03:17 AM
Hmmm....maybe it doesn't allow subselects in check constraints?  I grep'd for default_party_id and couldn't find it anywhere, hmmm...
Collapse
3: Response to alter table add constraint (response to 1)
Posted by Daryl Biberdorf on 11/12/01 04:24 AM

According to the PostgreSQL docs:

Currently, CHECK expressions cannot contain sub-selects nor refer to variables other than fields of the current row.

Looks like this will probably need a trigger.

Collapse
4: Response to alter table add constraint (response to 1)
Posted by Jonathan Ellis on 11/12/01 05:35 AM
Ah... thanks, Daryl, I missed that.