title: PostgreSQL: constraint which affects multiple tables
body: <p>I have a hierarchical structure of entities, each of which might have more than one name, so that I have a separate table for names. A simplified SQL Schema looks as follows: </p>
  
  <pre><code>CREATE TABLE users (
    id SERIAL NOT NULL PRIMARY KEY,
    -- some fields
  );
  
  CREATE TABLE entities (
    id SERIAL NOT NULL PRIMARY KEY,
    parent_id INTEGER NOT NULL,
    owner_id INTEGER NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES entities(id),
    FOREIGN KEY (owner_id) REFERENCES users(id)
  );
  
  CREATE TABLE entity_names (
    id SERIAL NOT NULL PRIMARY KEY,
    entity_id INTEGER NOT NULL,
    name VARCHAR(30) NOT NULL,
    FOREIGN KEY (entity_id) REFERENCES entities(id)
  );
  </code></pre>
  
  <p>And I need to ensure that there's no entities with duplicate name (of course, with the same <code>parent_id</code> and <code>owner_id</code>).</p>
  
  <p>I can ensure that right before adding a name to <code>entity_names</code> with the query like this:</p>
  
  <pre><code>SELECT COUNT(t.id)
    FROM entity_names n
    JOIN entities e ON n.entity_id = e.id
    WHERE e.parent_id = 123 and e.owner_id = 456 and n.name = 'foo'
  </code></pre>
  
  <p>But I'm wondering if it's possible (and sane) to implement this constraint in the database?</p>