mysql - 支持事务、行级锁定和外键

Question

出于某种原因我无法创建此表:

CREATE TABLE user_role (
  user_id VARCHAR(20) NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (user_id)
    REFERENCES users(user_id),
  FOREIGN KEY (role_id)
    REFERENCES roles(role_id)
);

下面类似的表格没有问题:

CREATE TABLE role_perm (
  role_id INTEGER UNSIGNED NOT NULL,
  perm_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (role_id)
    REFERENCES roles(role_id),
  FOREIGN KEY (perm_id)
    REFERENCES permissions(perm_id)
);

我得到的错误信息是:

#1005 - 无法创建表“test.user_role”(错误号:150)(详细信息...)
支持事务、行级锁定和外键

有什么想法吗？

Answer 1

参见 http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed.

If 很难猜测，因为您没有提供roles 和permissions 表的定义，而是解释文档。 ..

• ...为了在列上有一个外键，您必须在“目标”列上有一个索引。
• ...为了在列上有外键，“源”和“目标”列必须具有相同的类型(包括相同的大小(如果适用))。<
• ...为了在列上有外键，两个表必须使用 InnoDB 引擎。