MySQL中外键引用联合主键中非首列失效的原因咨询

Let's break down exactly what's happening here—this all boils down to how InnoDB handles indexes for foreign key constraints.

The core rule for InnoDB foreign keys #

InnoDB requires that the column(s) you reference in the parent table have an index where those columns are the leading prefix. This isn't about uniqueness (though unique indexes work too)—it's about performance: the database needs a fast way to verify referenced values exist, and to find related rows if you perform actions like deleting/updating the parent table with cascading rules.

Why your first code works #

When you define the composite primary key PRIMARY KEY(col1, col2) on tab1, InnoDB automatically creates a clustered index with col1 as the first column, followed by col2. This index's leading prefix is col1, so when you create a foreign key in tab2 referencing tab1.col1, InnoDB can use this existing composite index to satisfy the index requirement.

And yes, even though col1 can have duplicate values (like your example of (1,1) and (1,2)), that's totally allowed for foreign keys. Foreign keys only care that the referenced value exists in the parent table—they don't require the referenced column to be unique (that's a rule for primary keys/unique indexes, not foreign keys).

Why your second code fails #

When you try to reference tab1.col2 in the foreign key, there's no index in tab1 where col2 is the leading prefix. The existing composite index starts with col1, so it can't be used to quickly look up values by col2 alone. InnoDB can't create the foreign key without this required index, hence the Missing index for constraint error.

Fixing the second code #

To make the second code execute successfully, you just need to add an index on tab1.col2 (or a composite index where col2 is the first column):

CREATE TABLE tab1 ( 
  col1 int, 
  col2 int, 
  PRIMARY KEY(col1, col2),
  INDEX idx_col2 (col2) -- Add this index to support the foreign key
);
CREATE TABLE tab2 ( 
  col1 int, 
  FOREIGN KEY (col1) REFERENCES tab1(col2) 
);

内容的提问来源于stack exchange，提问作者Abhay Patil