Question 201995 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Database relationship between User, Permission and multiple resources

I have 4 entities :

  • User(id*, ...)
  • Permission(user_id*, resource_id*, resource_type, role)
  • Resource1(id*, ...)
  • Resource2(id*, ...)

Resource1 and Resource2 share no semantic relation and are completely independant.

How can I force my database to ensure that Permission.resource_id exists in one and exactly one of Resource1 and Resource2 ?

I know I can use an intermediate table Resource(id*) and add a procedure to add the record into that table upon an INSERT statement on Resource1/2 and set a foreign key constraint on Permission.resource_id to Resource.id.

Is there any solution to implement that logic without the Resource table ? I would prefer to use SQLite or PostgreSQL, otherwise I’m happy with a solution in another SQL database engine.

Tags:

2

Answers


  1. 0

    In PostgreSQL, you can utilize check constraints along with a deferrable foreign key constraint to enforce the constraint.

    View

    CREATE VIEW my_view AS
SELECT id, 'Resource1' AS type FROM Resource1
UNION ALL
SELECT id, 'Resource2' AS type FROM Resource2;

    Permission table:

    CREATE TABLE Permission (
  user_id INTEGER,
  resource_id INTEGER,
  resource_type TEXT,
  role TEXT,

  -- Add a check constraint to ensure the resource_type is either 'Resource1' or 'Resource2'
  CHECK (resource_type IN ('Resource1', 'Resource2')),

  -- Add a foreign key constraint with deferrable option to allow for deferred checks
  FOREIGN KEY (resource_id, resource_type) my_view (id) DEFERRABLE INITIALLY DEFERRED
);

    Trigger

    CREATE OR REPLACE FUNCTION check_foreign_id() RETURNS TRIGGER AS $$
BEGIN
  -- Your desired conditions based on the query
  IF NEW.foreign_id NOT IN (SELECT id FROM some_table WHERE condition = true) THEN
    RAISE EXCEPTION 'Foreign key constraint violation.';
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_foreign_id_trigger
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_foreign_id();
    Login or Signup to reply.
  2. 0

    Modify your Permission table to include both resource1_id and resource2_id. Then add a check constraint to make sure exactly 1 of them is null. You can also cross check resource_type vs. the non-null resource id.

    Permission(user_id*, resource1_id*, resource2_id* resource_type, role);

    Sample table DDL with constraints.

    create table Permission(user_id   integer not null 
                                  references users(id) 
          , resource1_id  integer references resource1(id) 
          , resource2_id  integer references resource1(id) 
          , resource_type text
            -- exactly 1 of resource ids must be null
          , constraint exactly_one_resource check( num_nulls(resource1_id, resource2_id) = 1)
            -- not null resource id must agree with resource_type
          , constraint id_matches_type check (   resource_type = 'Resource1' and resource1_id is not null
                                              or resource_type = 'Resource2' and resource2_id is not null
                                              )  
                                              
          );

    This does not require any triggers.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search