title: Can I create a foreign key constraint that also requires an arbitrary row on the parent table to be a certain value?
body: <p>As an example of this, let's say I have two simplistic tables and an ENUM:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TYPE role AS ENUM (
    'admin',
    'seller'
  );
  
  CREATE TABLE users (
    id serial PRIMARY KEY,
    role role NOT NULL
  );
  
  CREATE TABLE orders (
    id serial PRIMARY KEY,
    seller_id int NOT NULL REFERENCES users(id), -- Should only allow for 'role' of 'seller'
    cost currency NOT NULL
  );
  </code></pre>
  
  <p>In order to create an order, there must be a corresponding user that was responsible for the sale. Only a user that has a <code>role</code> of <code>seller</code> should be able to create an order (e.g. a user with a <code>role</code> of <code>admin</code> should not be able to be a <code>seller</code>).</p>
  
  <p>However, a user can have multiple roles. Currently there is no database-level check to ensure that <code>seller_id</code> can only refer to a <code>user</code> with a <code>role</code> of <code>seller</code>. Having checked the <a href="https://www.postgresql.org/docs/current/ddl-constraints.html" rel="nofollow noreferrer">contstraints documentation</a> I'm not completely sure this is possible without creating <em>another</em> table specifically for a seller (even though they function exactly the same otherwise).</p>
  
  <p>Is there something obvious I'm missing?</p>