title: SQL Server Foreign Key
body: <p>I'm trying to create this database with the following relations in SQL Server and I get this error:</p>
  
  <blockquote>
    <p>Msg 1776, Level 16, State 0, Line 11<br>
    There are no primary or candidate keys in the referenced table 'Consumable' that match the referencing column list in the foreign key 'FK_<em>Recipe</em>_Ingredie__59FA5E80'.<br>
    Msg 1750, Level 16, State 0, Line 11<br>
    Could not create constraint. See previous errors.</p>
  </blockquote>
  
  <p>What am I doing wrong?</p>
  
  <p>Here's my code;</p>
  
  <pre><code>CREATE TABLE Consumable
  (
      c_ID int NOT NULL,
      Name varchar(32) NOT NULL,
      Amount int,
      Unit varchar(8) NOT NULL CHECK (unit IN ('ml', 'g', 'pieces', 'unknown')),
      CONSTRAINT PK_Consumable PRIMARY KEY (c_ID, Name)
  )
  
  CREATE TABLE Recipe
  (
      Name varchar(64) PRIMARY KEY NOT NULL,
      Type varchar(32),
      Description varchar(512),
      IngredientsID int NOT NULL FOREIGN KEY REFERENCES Consumable(c_ID) ON DELETE CASCADE
  )
  
  CREATE TABLE Kitchen
  (
      K_ID int PRIMARY KEY NOT NULL IDENTITY,
      IngredientsID int FOREIGN KEY REFERENCES Consumable(c_ID) ON DELETE CASCADE
  )
  </code></pre>