title: Cross table constraints in PostgreSQL
body: <p>Using PostgreSQL 9.2.4, I have a table <code>users</code> with a 1:many relation to the table <code>user_roles</code>. The <code>users</code> table stores both employees and other kinds of users.</p>
  
  <pre><code>                                       Table "public.users"
      Column       |            Type   |                      Modifiers
  -----------------+-------------------+-----------------------------------------------------
   uid             | integer           | not null default nextval('users_uid_seq'::regclass)
   employee_number | character varying |
   name            | character varying |
  
  Indexes:
      "users_pkey" PRIMARY KEY, btree (uid)
  Referenced by:
      TABLE "user_roles" CONSTRAINT "user_roles_uid_fkey" FOREIGN KEY (uid) REFERENCES users(uid)
  
                                        Table "public.user_roles"
      Column |            Type   |                            Modifiers
  -----------+-------------------+------------------------------------------------------------------
   id        | integer           | not null default nextval('user_roles_id_seq'::regclass)
   uid       | integer           |
   role      | character varying | not null
  Indexes:
      "user_roles_pkey" PRIMARY KEY, btree (id)
  Foreign-key constraints:
      "user_roles_uid_fkey" FOREIGN KEY (uid) REFERENCES users(uid)
  </code></pre>
  
  <p>I want to ensure that the column <code>users.employee_number</code> cannot be <code>NULL</code> if there is a related row where <code>user_roles.role_name</code> contains an employee role name. That is, I want the database to enforce the constraint that for some roles, <code>users.employee_number</code> must have a value, but not for others.</p>
  
  <p>How can I accomplish this, preferably without user-defined functions or triggers? I found (<a href="http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/" rel="nofollow noreferrer">blog post</a>, <a href="https://stackoverflow.com/questions/16314372/ms-sql-server-cross-table-constraint">SO Answer</a>) that SQL Server supports indexed views, which sounds like it would serve my purpose. However, I assume that materialized views will not work in my case, since they are not dynamically updated.</p>