Mysql Foreign Key must reference entire primary key of parent table?

Question

I'm working on a small pizza delivery website and I ran in to a small problem with the MySQL tables.

I found this on Stackoverflow: https://stackoverflow.com/a/10322293/80907

It mentions the following:

• Both tables have to use the INNODB engine.
• "In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. "

The first isn't really a problem, but the second rule is where I'm scratching my head.

This is a website where you can order pizzas, so I'm saving all data on the users and their order in the database.

Here's a screenshot of what I'm about to write out:

So I'll have a "Users" table and an "Orders" table. The Users will have to have a one-to-many relationship to Orders. Simply put, the order is identified by the user who created it. So it's one-to-many, identifying.

This means that the "Orders" table will have a foreign key, such as "Users_id".

The problem arises when you have to make a table for the many-to-many relationship between the Pizzas table and the Orders table.

This table, let's call it "Order_Details"(MySQL Workbench automatically called it "Orders_has_Pizzas") must reference both "Orders" and "Pizzas".

Now, since "Orders" already references the Users table in an identifying relationship, that's part of the primary key of "Orders".

And let's get that rule out once again:

• "In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. "

What this means is that you must reference the entire primary key. If I delete the "Order_Users_id" key, I'll get a 1005 error upon trying to create the database.

My question is simply: is there a way around this? Because as it is right now, I have that User id mentioned in 3 different tables.

Or, am I not understanding it properly and is it indeed necessary to do this for the sake of not having to query different tables for that data?

EDIT: People seem to disagree with me on the relation between Users and Orders being identifying.

I don't see how an individual order can be identified without knowing the id of the user. After the order is made, someone is going to have to deliver the pizza, meaning they'll need to know where to deliver it. That data is in the Users table. Therefor, the Users_id is part of the identity of a single order.

That's how I see it anyway. If I'm wrong, please explain why.

EDIT 2: Thanks to a_horse_with_no_name for clarifying the concept of "identity" in terms of databases, I see the error of my logic now. Info can be found in the comments.

Answer 1

To answer your original question, no, InnoDB foreign key constraints are not required to reference the entire primary key of the referenced table.

In other words, both of the following work in InnoDB:

mysql> ALTER TABLE Orders_has_Pizzas ADD FOREIGN KEY (Orders_id) 
    REFERENCES Orders (id);
Query OK, 0 rows affected (0.63 sec)

mysql> ALTER TABLE Orders_has_Pizzas ADD FOREIGN KEY (Orders_id,Orders_Users_id) 
    REFERENCES Orders (id, Users_id);
Query OK, 0 rows affected (0.02 sec)

In fact, InnoDB allows a foreign key to reference any indexed column, even if it's not part of the primary key:

mysql> CREATE TABLE Foo (fooid INT PRIMARY KEY, nonunique INT, KEY (nonunique));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE Bar (barid INT PRIMARY KEY, foo_nonid INT, FOREIGN KEY (foo_nonid) 
    REFERENCES Foo(nonunique));
Query OK, 0 rows affected (0.06 sec)

However, this is not standard SQL and I don't recommend doing it. It means that a row in Bar could reference more than one row in the parent table Foo. Which means a JOIN between these two on the foreign-primary key relationship could unexpectedly create a sort of mini-Cartesian product.

In the Orders table, it's possible for either column of a compound primary key to contain duplicates. Which means a given row in Orders_has_Pizzas could theoretically reference multiple Orders.

As for the question about an identifying relationship, I would agree that Orders has an identifying relationship with respect to Users. That is, it makes no sense for an order to exist with no referenced user.

But in a table where we use an auto-incrementing mechanism to generate unique id's, it seems redundant and unnecessary to add the extra column to the PK. Why would we need Orders to contain the users id? The id alone is guaranteed unique and therefore sufficient to uniquely address each row.

I would say that's a practical choice, whereas the theory would guide us to create the compound primary key in Orders.

It becomes more clear in a many-to-many table like your Orders_has_Pizzas. This table has an identifying relationship with both Orders and Pizzas. The primary key consists of two foreign keys, one referencing Orders and the other referencing Pizzas. There's no need for an auto-increment PK at all.

Some people add a superfluous auto-increment id for many-to-many tables, for the sake of a convention that every table has to have a single-column automatically-generated primary key. But there's no theoretical or practical reason to do this.

CREATE TABLE Orders_has_Pizzas (
  id INT AUTO_INCREMENT PRIMARY KEY, -- what is this column for?
  Orders_id INT,
  Orders_Users_id INT,
  Pizzas_id INT,
);