I am trying to develop a database table column that is contrainted to a
subset of another table column. I have tried using foreign key, check,
and inheritance, but I cannot figure out how to do it. I have a
user_data table that has various user columns including name and the
bool column write_access. I have another table to record user actions
and this table needs to have a user column whose value can only be one
of "SELECT name from user_data where write_access = 't'". Any
suggestions about how I could accomplish this? I asked a similar
question in the past and Qingqing mentioned pg DOMAINS, but this does
not really fit with what I want to do. I could seperate my users into 2
or more tables "write_access, read_only, other" but I would rather keep
all user data in the same place.
CREATE TABLE user_data(
name varchar(32),
write_access bool DEFAULT 'f'
);
CREATE TABLE actions(
action varchar(32),
user varchar(32) -- somehow make sure user = user_data.name where
user_data.write_access = 't'
);
Thanks,
Dale