> One way to do this is to add a write_access column to actions and use
> a constraint to force it to be true.
>Create a UNIQUE key of
> (name, write_access) for user_data and then add a FOREIGN KEY
> reference from (name, write_access) in actions to (name, write_access)
> in user_data.
Yes the name must unique indexed but couldn't force the write_access
to always 'true'.
I may suggest create a trigger function to validate insert to table actions:
CREATE OR REPLACE FUNCTION validate_actions_insert() RETRUNS OPAQUE AS '
DECLARE
rs RECORD;
BEGIN
SELECT INTO rs * FROM user_data WHERE name = NEW.user and write_access = 't';
IF NOT FOUND THEN
RAISE EXCEPTION ''writing access forbidden for user '', NEW.user;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER tg_actions BEFORE INSERT OR UPDATE ON actions
FOR EACH ROW EXECUTE PROCEDURE validate_actions_insert();
You may need create another trigger for table user_data before update
for reverse validation.
