title: MySQL: Creating a table that with a foreign key that references a primary key made up of foreign keys
body: <p>I have a table of books, which stores book IDs and titles, authors, etc., a table of customers which has customer IDs (PK), customer name, birthday. Then there is a table of transactions, which includes the BookId and the CustomerID as foreign keys. The primary key of this transaction table is the two foreign key columns.</p>
  
  <p>The problem is now I am creating a table of book ratings based on transactions which have occurred (So a customer can't rate a book they don't own, or the same book twice.). This table must contain the bookID, the customerID, and the rating info. I'm trying to constrain the table so that each bookID and customerID row must reference an existing row in the transaction table. However, none of the constraints I've tried so far work. Any help at all would be greatly appreciated.</p>
  
  <p>Here's my tables at the moment:</p>
  
  <pre><code>CREATE TABLE eBook
   (      BookTitle   VARCHAR(50),
      BookID      CHAR(13) NOT NULL,
      BookPub     CHAR(4) NOT NULL,
      BookDate    DATE NOT NULL,
      BookHard    BOOL NOT NULL,
      BookSize    INT NOT NULL, 
  CONSTRAINT PKBookID PRIMARY KEY (BookID)
  );
  
  CREATE TABLE customer
  (       CustID      CHAR(9) NOT NULL,
      CustSSN     CHAR(11) NULL,
      CustName    VARCHAR(50) NOT NULL,
      CustBirth   INT(4) NOT NULL, 
  CONSTRAINT PKCustID PRIMARY KEY (CustID),
  CONSTRAINT UniqSSN UNIQUE (CustSSN)
  );
  
  CREATE TABLE Buys
  (   BuysID      CHAR(5) NOT NULL                    
  BookID      CHAR(13) NOT NULL,
  CustID      CHAR(9) NOT NULL,
  TransDate   DATETIME NOT NULL,
  Price       DECIMAL(5,2) NOT NULL,
  CONSTRAINT FK1 FOREIGN KEY (BookID) REFERENCES eBook (BookID)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT FK2 FOREIGN KEY (CustID) REFERENCES customer (CustID)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT PKBuys PRIMARY KEY (BookID, CustID)
  );
  
  CREATE TABLE Rating
  (   BookID      CHAR(13) NOT NULL,
  CustID      CHAR(9) NOT NULL,
  RatingID    CHAR(5) NOT NULL,
  Rating      INT(1) NOT NULL,
  RatingDate  DATE NOT NULL,
  CONSTRAINT FK13 FOREIGN KEY (BookID, CustID) REFERENCES buys (BookID, CustID)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT PKRating PRIMARY KEY (RatingID)
  );
  </code></pre>