Data inconsistency prohibition if a table refers to another via two many-to-many relationships
You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company.

This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships:

Realm          Company
   \           /     \
    \         /       \
     \       /         \
    CompanyRealm     Project
             \         /
              \       /  
               \     /    
             ProjectRealm


The common solution, if you want to enforce this via DRI (Declarative Referential Integrity), ie. through usual UNIQUE, FOREIGN KEY and CHECK constraints - and not though triggers - is to use composite keys for the ProjectRealm REFERENCES CompanyRealm and for the ProjectRealm REFERENCES Project foreign keys.

You will need to replace the company_realm_id with 2 columns (company_id, realm_id) and add a composite UNIQUE constraint on each of the two referenced tables (or modify their PRIMARY KEY):

Company [id]

Realm [id]

CompanyRealm [id, realm_id=&gt;Realm.id, company_id=&gt;Company.id,
              UNIQUE(company_id, realm_id)]

Project [id, company_id=&gt;Company.id,
         UNIQUE(company_id, id)]

ProjectRealm [id, project_id, company_id, realm_id,
              (company_id, project_id) =&gt; Project (company_id, id),
              (company_id, realm_id) =&gt; CompanyRealm (company_id, realm_id)]


See also several other questions, where the same (or quite similar) problem appears. The diamond pattern is obvious in all:


Best data modelling approach to handle redundant foreign keys in relational model
and the nice answer and logical models and diagrams by MDCCL.
Foreign Key Constraint over two tables
How to represent it with UNIQUE indexes and foreign keys?
Many to Many and Weak Entities
Constrains relationship