支持事务，行级锁定和外键 [英] Supports transactions, row-level locking, and foreign keys

问题描述

由于某些原因，我无法创建该表:

For some reason I cannot create this table:

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)
);

以下类似表格没有问题:

The following similar table has no problems:

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 - Can't create table 'test.user_role' (errno: 150) (Details...) Supports transactions, row-level locking, and foreign keys

#1005 - Can't create table 'test.user_role' (errno: 150) (Details...) Supports transactions, row-level locking, and foreign keys

有什么想法吗?

推荐答案

请参见

如果重新创建已删除的表，则该表必须具有符合引用该表的外键约束的定义.如前所述，它必须具有正确的列名和类型，并且必须在引用的键上具有索引.如果不满足这些条件，MySQL将返回错误1005，并在错误消息中引用错误150，这意味着未正确形成外键约束.

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.

如果很难猜出 ，因为您没有提供roles和permissions表的定义，而是为了解释文档...

If is rather difficult to guess as you didn't provide the definitions for the roles and permissions tables but to paraphrase the doc...

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

• ...in order to have a foreign key on a column, you must have an index on the "target" column.
• ...in order to have a foreign key on a column, both "source" and "target" columns must have the same type (incl. the same size if applicable).
• ...in order to have a foreign key on a column, both tables must use InnoDB engine.

这篇关于支持事务，行级锁定和外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！