MySQL Err No 150外键约束格式错误：创建商品与分类多对多关联表失败的问题排查

Problem Description #

I'm trying to create a many-to-many join table product_categories for products and categories in MySQL with the InnoDB storage engine. All IDs use UUID4, so the field type is char(36).

Here's my CREATE TABLE statement for the join table:

create table product_categories (
    product_id char(36) not null,
    category_id char(36) not null,
    primary key (product_id, category_id),
    constraint fk_product_categories_category foreign key (category_id) references categories (id) on delete cascade,
    constraint fk_product_categories_product foreign key (product_id) references products (id) on delete cascade
);

The issue seems to be with the category_id foreign key—if I remove that constraint, the table creates fine. The categories table has an id field with a matching type (char(36)), here's its create statement (abbreviated):

CREATE TABLE `categories` (
    `id` char(36) NOT NULL,
    -- other fields omitted
)

But I still get this error: [HY000][1005] Can't create table product_categories (errno: 150 "Foreign key constraint is incorrectly formed"). What am I missing?

Troubleshooting & Solutions #

I’ve run into this exact UUID foreign key issue a few times—let’s walk through the most likely fixes:

• The categories.id field isn’t a primary key or unique index
  InnoDB requires that any column referenced by a foreign key is either the table’s primary key or has a unique index. Your abbreviated categories create statement doesn’t show a PRIMARY KEY (id) clause—if you skipped that step, the constraint will fail even with matching data types. Double-check that categories defines id as its primary key:

  CREATE TABLE `categories` (
      `id` char(36) NOT NULL PRIMARY KEY,
      -- other fields
  ) ENGINE=InnoDB;
  

• The categories table isn’t using InnoDB
  Foreign key constraints only work with the InnoDB storage engine. If your categories table was created with MyISAM (an old default in some MySQL versions) or another engine, error 150 will pop up. Add ENGINE=InnoDB to your categories create statement if it’s missing.

• Character set or collation mismatch
  Even with matching char(36) types, a discrepancy in character set or collation between product_categories.category_id and categories.id breaks the foreign key. For example, if categories uses utf8mb4 but your join table uses utf8, or their collations differ (like utf8_general_ci vs utf8_unicode_ci). Explicitly set matching values for both tables:

  CREATE TABLE `categories` (
      `id` char(36) NOT NULL PRIMARY KEY,
      -- other fields
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  
  create table product_categories (
      product_id char(36) not null,
      category_id char(36) not null,
      primary key (product_id, category_id),
      constraint fk_product_categories_category foreign key (category_id) references categories (id) on delete cascade,
      constraint fk_product_categories_product foreign key (product_id) references products (id) on delete cascade
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  

• Case sensitivity (rare but possible)
  On case-sensitive filesystems (like Linux), MySQL treats table/column names as case-sensitive. Double-check that the referenced column is exactly id (lowercase) in the categories table—if it’s ID (uppercase) instead, the foreign key reference will fail even if you wrote categories (id) in your constraint.

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