title: Postgres Composite Primary Key Dependency Issue
body: <p>I'm taking a database course at Johns Hopkins in Maryland and have a question. I already emailed my professor and he knows I'm asking this question here and he's cool with that. So I'm developing a COOKBOOK DB in Postgres and I have an interesting problem I’ve been facing in Postgres where I just can’t seem to be able to build the PRICE table. I have a nice Cookbook ERD but apparently can't post until my reputation is at least 10. I'll do my best to describe the ERD. There are three factoring tables relating to PRICE. These are INGREDIENT, SUBSTITUTION, and PRICE.</p>
  
  <p>Here's a link to the ERD (notice the 1:M for INGREDIENT to SUBSTITUTION):
  [<a href="http://i.stack.imgur.com/YEExS.jpg" rel="nofollow">ERD</a>]</p>
  
  <p>I can have one INGREDIENT with potentially many SUBSTITUTIONS and a possible one-to-one between SUBSTITUTION and PRICE (one PRICE per SUBSTITUTION if a PRICE is known). If the PRICE is known then the PRICE is able to define a tuple with a composite primary key: (price_id, ingredient_id (fk), substitution_id (fk))</p>
  
  <p>The challenge I’m facing is that Postgres SQL is not allowing me to establish this relationship and I’m not exactly sure why. I’ve set the keys in SUBSTITUTION to have a UNIQUE constraint so that shouldn’t be the problem. The only thing I can think of is that the ingredient_id in SUBSTITUTION is a foreign key to INGREDIENT and therefore may not be physically established in SUBSTITUTION but the error I'm getting doesn't suggest that. This is what I’m getting in the terminal (<strong>first describing SUBSTITUTION</strong>):</p>
  
  <pre><code>   cookbook=# \d+ SUBSTITUTION
                                                               Table "public.substitution"
     Column       |         Type          |                                Modifiers                                 | Storage  | Description 
     --------------------+-----------------------+--------------------------------------------------------------------------+----------+-------------
     substitution_id    | integer               | not null default nextval('subsitution_substitution_id_seq'::regclass)    | plain    | 
     ingredient_id      | integer               | not null default nextval('subsitution_ingredient_id_seq'::regclass)      | plain    | 
     name               | character varying(50) | not null                                                                 | extended | 
     measurement_ref_id | integer               | not null default nextval('subsitution_measurement_ref_id_seq'::regclass) | plain    | 
     metric_unit        | character varying(25) | not null                                                                 | extended | 
     Indexes:
     "subsitution_pkey" PRIMARY KEY, btree (substitution_id, ingredient_id)
     "uniqueattributes" UNIQUE, btree (substitution_id, ingredient_id)
     Foreign-key constraints:
     "subsitution_ingredient_id_fkey" FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
     "subsitution_measurement_ref_id_fkey" FOREIGN KEY (measurement_ref_id) REFERENCES measurement_ref(measurement_ref_id)
     Has OIDs: no
  
     cookbook=# create table price(
     price_id serial not null,
     ingredient_id serial references substitution(ingredient_id),
     cookbook(# substitution_id serial references substitution(substitution_id),
     cookbook(# usdollars smallint not null,
     cookbook(# availability season,
     cookbook(# seasonal boolean,
     cookbook(# primary key (price_id, ingredient_id, substitution_id)
     cookbook(# );
     NOTICE:  CREATE TABLE will create implicit sequence "price_price_id_seq" for serial column "price.price_id"
     NOTICE:  CREATE TABLE will create implicit sequence "price_ingredient_id_seq" for serial column "price.ingredient_id"
     NOTICE:  CREATE TABLE will create implicit sequence "price_substitution_id_seq" for serial column "price.substitution_id"
     NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "price_pkey" for table "price"
     ERROR:  there is no unique constraint matching given keys for referenced table "substitution"
  </code></pre>