Data inconsistency prohibition if a table refers to another via two many-to-many relationships I have following database design (by =&gt; foreign key constraints are depicted):


Company[id]
CompanyRealm[id, company_id=>Company.id]many2many

Project[id, company_id=>Company.id]
ProjectRealm[id, project_id=>Project.id, company_realm_id=>CompanyRealm.id]many2many


Problem is that provided database design allows inconsistent data. For example:


Company1(id=1)
Company2(id=2)
CompanyRealm(id=11, company_id=1)
Project(id=33, company_id=2)
ProjectRealm(id=44, project_id=33, company_realm_id=11)


(Company can have many Realms, Project belong to Company and  may concern any number of company's Realms)

ProjectRealm refers to two different companies:


Company1 (via CompanyRealm); and
Company2 (via Project). 


Is something wrong with my DB design?
If yes - which prescriptions are violated?
If no - how to prevent incorrect data insertion (via constraint? trigger with exception?)
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