title: Double Foreign key in postgresql
body: <p>I am trying to use a double primary key as a foreign key.</p>
  
  <pre><code>Create table AAA (
     AAA_id int primary key
  );
  
  create table BBB (
     AAA_id int,
     BBB_name character varying(20),
     primary key (AAA_id, BBB_name)
  );
  
  create table CCC (
      AAA_id,
      BBB_name,
      DDD_id,
  
      ... ???
  );
  </code></pre>
  
  <p>table AAA is an object</p>
  
  <p>table BBB is many to one with AAA, and holds aliases of AAA</p>
  
  <p>I am trying to create a pivot table, CCC which holds a many to one between DDD and BBB.</p>
  
  <p>I guess I want something like</p>
  
  <pre><code>create table CCC (
      AAA_id,
      BBB_name,
      DDD_id,
      foreign key (AAA_id, BBB_name) references BBB(AAA_id, BBB_name) on update cascade
  );
  </code></pre>
  
  <p>where both AAA_id and BBB_name are foreign keys, but they are also always referring to the same row in BBB.</p>
  
  <p>but of course that's not valid. what is the best way to produce this type of behavior in postgreSQL?</p>