Relationship between User, Permission and multiple resources without intermediate table

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 (are independent). How can I force my database to ensure Permission.resource_id exists in one and exactly one of Resource1 and Resource2?

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. But how to implement that logic without the Resource table? I would prefer to use SQLite or PostgreSQL, but I'm happy with a solution in any other SQL database engine.

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();

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

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.