Обсуждение: sub query constraint

    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

On Mon, Mar 28, 2005 at 16:13:59 -0600,
  Dale Sykora <dalen@czexan.net> wrote:
>
> 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'
> );

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.

> 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.

Yudie Pg wrote:
>>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.
>
Bruno and Yudie,
    Thanks for the replies.  I will read up on triggers and give that a try.

Thanks,

Dale

Yudie Pg wrote:
> CREATE OR REPLACE FUNCTION validate_actions_insert() RETRUNS OPAQUE AS '
CREATE OR REPLACE FUNCTION validate_actions_insert() RETURNS 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;
       RAISE EXCEPTION 'writing access forbidden for user %', NEW.user;
>   END IF;
>
>   RETURN NEW;
> END;
> ' LANGUAGE plpgsql;
  $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER tg_actions BEFORE INSERT OR UPDATE ON actions
> FOR EACH ROW EXECUTE PROCEDURE validate_actions_insert();

Yugi,
    I made a few minor modifications as shown above and the trigger
function works great.  I think I'll also use triggers to keep a history
of record changes for auditing.

Thanks,

Dale