MariaDB: InnoDB 外键约束错误
引言
外键是表中的一个字段（或一组字段），用于唯一标识另一个表中的一行。包含外键的表称为子表，包含候选键的表称为被引用表或父表。外键的目的是标识被引用表中的特定行。因此，要求外键等于主表中某行的候选键，或者为空值（NULL 值）。这称为两个表之间的引用完整性约束。由于违反这些约束可能会导致许多数据库问题，大多数数据库管理系统都提供了机制来确保每个非空外键都对应于被引用表中的一行。考虑以下简单示例
create table parent (
id int not null primary key,
name char(80)
) engine=innodb;
create table child (
id int not null,
name char(80),
parent_id int,
foreign key(parent_id) references parent(id)
) engine=innodb;
据我所知，以下 MariaDB 和/或 MySQL 存储引擎支持外键
- InnoDB（包括 innodb_plugin 和 XtraDB）
- PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/)
- SolidDB for MySQL (http://sourceforge.net/projects/soliddb/)
- ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ 和 http://scaledb.com/pdfs/TechnicalOverview.pdf)
- MySQL Cluster NDB 7.3 或更高版本 (https://dev.mysqlserver.cn/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)
MariaDB 外键语法在 https://mariadb.com/kb/en/mariadb/foreign-keys/ 中有文档说明（MySQL 在 https://dev.mysqlserver.cn/doc/refman/5.5/en/innodb-foreign-key-constraints.html）。虽然大多数语法在解析 CREATE TABLE 或 ALTER TABLE 子句时会被解析和检查，但 InnoDB 内部仍可能发生几种错误情况。是的，InnoDB 有自己的内部外键约束解析器（在 dict0dict.c 的 dict_create_foreign_constraints_low() 函数中）。
然而，在早于 5.5.45 和 10.0.21 版本的 MariaDB 中，CREATE TABLE 或 ALTER TABLE 以及 SHOW WARNINGS 显示的错误消息信息量不大或不清楚。如果您执行 SHOW ENGINE INNODB STATUS，会有额外的错误消息提供帮助，但这并非理想的解决方案。在这篇博客中，我将使用 MariaDB 5.5.44 介绍一些最常见的错误情况，并说明这些错误消息在 MariaDB 5.5.45 和 10.0.21 中是如何改进的。我将使用默认的 InnoDB（即 XtraDB），但 innodb_plugin 的工作方式非常相似。
约束名称不唯一
外键约束名称在数据库中必须是唯一的。然而，错误消息不清楚，留下了很多疑问
-------------- CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.45 sec) -------------- CREATE TABLE t2 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") -------------- show warnings -------------- +---------+------+--------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") | | Warning | 1022 | Can't write; duplicate key in table 't2' | +---------+------+--------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
这些消息帮助不大，因为这里有两个外键约束。查看 SHOW ENGINE INNODB STATUS，我们可以获得更好的消息
show engine innodb status -------------- ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 12:37:48 7f44a1111700 Error in foreign key constraint creation for table `test`.`t2`. A foreign key constraint of name `test`.`test` already exists. (Note that internally InnoDB adds 'databasename' in front of the user-defined constraint name.) Note that InnoDB's FOREIGN KEY system tables store constraint names as case-insensitive, with the MySQL standard latin1_swedish_ci collation. If you create tables or databases whose names differ only in the character case, then collisions in constraint names can occur. Workaround: name your constraints explicitly with unique names.
在 MariaDB 5.5.45 和 10.0.21 中，消息已得到明显改进
CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.14 sec) -------------- CREATE TABLE t2 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 121) -------------- show warnings -------------- +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 121 | Create or Alter table `test`.`t2` with foreign key constraint failed. Foreign key constraint `test/test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`). | | Error | 1005 | Can't create table 'test.t2' (errno: 121) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
无索引
被引用表中包含被引用列作为首列的字段应该有一个索引。
create table t1(a int, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.46 sec) -------------- create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table 'test/t2' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. | | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
没问题，但我们再次不知道是哪个外键。和以前一样，SHOW ENGINE INNODB STATUS 输出中有更好的消息
LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:31 7f30e1520700 Error in foreign key constraint of table test/t2: foreign key a (a) references t1(a)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See https://dev.mysqlserver.cn/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
在 MariaDB 5.5.45 和 10.0.21 中，消息已得到明显改进
create table t1(a int, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.16 sec) -------------- create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key a (a) references t1(a)) engine=innodb. | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
找不到被引用表
外键约束引用的表应存在于 InnoDB 数据字典中。如果不存在
create table t1 (f1 integer primary key) engine=innodb -------------- Query OK, 0 rows affected (0.47 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t11(f1) -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------+ show engine innodb status -------------- LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:34 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2: foreign key (f1) references t11(f1): Cannot resolve table name close to: (f1)
这两条消息首先都引用了一个内部表名，而外键错误消息引用了一个不正确的名称。在 MariaDB 5.5.45 和 10.0.21 中，消息已得到明显改进
create table t1 (f1 integer primary key) engine=innodb -------------- Query OK, 0 rows affected (0.11 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t11(f1) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1). | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- show engine innodb status -------------- 150730 13:50:36 Error in foreign key constraint of table `test`.`t1`: Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1).
临时表
临时表不能有外键约束，因为临时表不存储在 InnoDB 数据字典中。
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+--------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- show engine innodb status -------------- LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_1: foreign key(a) references t1(a)) engine=innodb: Cannot resolve table name close to: (a)) engine=innodb -------------- alter table t1 add foreign key(b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
这些错误消息对用户没有太大帮助，因为没有打印实际的错误原因，并且外键错误引用了一个内部表名。在 MariaDB 5.5.45 和 10.0.21 中，这一点得到了明显改进
create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.04 sec) -------------- create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table `tmp`.`t2`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb. | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- alter table t1 add foreign key(b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `tmp`.`t1`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(b) references t1(a). | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
字段计数不匹配
外键字段列表和被引用字段列表中的字段数量应该完全相同。然而，这目前会引发以下错误
create table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.17 sec) -------------- alter table t1 add foreign key(a,b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-4856_1' (errno: 150) -------------- show warnings -------------- +-------+------+----------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------+ | Error | 1005 | Can't create table 'test.#sql-4856_1' (errno: 150) | +-------+------+----------------------------------------------------+ 1 row in set (0.00 sec) -----------------+ show engine innodb status; -----------------+ LATEST FOREIGN KEY ERROR ------------------------ 150730 15:15:57 Error in foreign key constraint of table test/#sql-4856_1: foreign key(a,b) references t1(a): Syntax error close to: 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_2: foreign key(b) references t1(a): Cannot resolve table name close to: (a)
错误消息不清楚，并且外键错误引用了一个内部表名。在 MariaDB 5.5.45 和 10.0.21 中，有额外的信息
create table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.14 sec) -------------- alter table t1 add foreign key(a,b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Foreign key constraint parse error in foreign key(a,b) references t1(a) close to ). Too few referenced columns, you have 1 when you should have 2. | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
不正确的级联
用户可以使用 ON UPDATE SET NULL 或 ON DELETE SET NULL 定义外键约束。然而，这要求被引用字段不能定义为 NOT NULL。目前，这种情况下的错误消息是
create table t1 (f1 integer not null primary key) engine=innodb -------------- Query OK, 0 rows affected (0.40 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+--------------------------------------------------------------------------------------------- --------+ show engine innodb status; --------+ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:37 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2: foreign key (f1) references t1(f1) on update set null: You have defined a SET NULL condition though some of the columns are defined as NOT NULL.
这两条错误消息都不太有用，因为第一条并没有真正说明外键约束是如何错误形成的，而后面一条也没有说明是哪个字段有问题。这一点在 MariaDB 5.5.45 和 10.0.21 中得到了改进
create table t1 (f1 integer not null primary key) engine=innodb -------------- Query OK, 0 rows affected (0.10 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in foreign key (f1) references t1(f1) on update set null close to on update set null. | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
类型不正确
外键字段和被引用字段的类型应匹配并使用相同的字符集。如果它们不匹配，您目前会收到
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb -------------- Query OK, 0 rows affected (0.47 sec) -------------- create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table 'test/t2' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. | | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) --------+ show engine innodb status; --------+ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:39 7f30e1520700 Error in foreign key constraint of table test/t2: foreign key(a) references t1(f1)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See https://dev.mysqlserver.cn/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
但是表 t2 中被引用字段 f1 是否有索引？因此，如果外键字段列表和被引用字段列表中有多个字段，我们应该在哪里查找错误？在 MariaDB 5.5.45 和 10.0.21 中，这通过以下方式得到了改进
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb -------------- Query OK, 0 rows affected (0.15 sec) -------------- create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
结论
有几种不同的方式可以错误地定义外键约束。在许多情况下，使用早期版本的 MariaDB（和 MySQL）时，这些情况产生的错误消息不是很清楚或有用。在 MariaDB 5.5.45 和 10.0.21 中，错误消息得到了明显改进，以帮助用户。当然，总有进一步改进的空间，因此非常欢迎反馈！
参考资料
我认为您指的是“临时表”（临时存在的表），而不是“时间表”（与时间相关的表）。
您是正确的，我将修复该错误。
非常感谢您这篇好文章，Jan！
您写道：字段计数不匹配。
alter table t1 add foreign key(a,b) references t1(a)
ERROR 1005 (HY000): Can’t create table ‘test.#sql-4856_1’ (errno: 150)
这是 bug 吗？语法错误应该在解析 ALTER TABLE 子句时确定。
5.6.24-72.2 Percona Server
alter table t1 add foreign key(id,b) references t1(id);
ERROR 1239 (42000): Incorrect foreign key definition for ‘foreign key without name’: Key reference and table reference don’t match
正如您所见，外键定义和引用定义中的字段数量不同。严格来说，这可以在语法解析期间检查，但由于 MariaDB/MySQL 并不完全支持外键（对于所有存储引擎），InnoDB 会对外键进行内部解析。
你好 Jan，我是 Victor，我遇到了一些类似的问题，想请您检查一下下面的代码：我想知道我是否可以实现这个场景
MariaDB [administracion]> describe usuario;
+————–+————-+——+———+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+———+———+—————-+
| id_usuario | int(15) | NO | PRI | NULL | auto_increment |
| mensaje | varchar(15) | NO | | NULL | |
| nick_usuario | varchar(15) | NO | | NULL | |
+————–+————-+——+———+———+—————-+
3 rows in set (0.01 sec)
MariaDB [administracion]> describe usuario1;
+————–+————-+——+———+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+———+———+—————-+
| id_usuario | int(15) | NO | PRI | NULL | auto_increment |
| mensaje | varchar(15) | NO | | NULL | |
| nick_usuario | varchar(15) | YES | | NULL | |
+————–+————-+——+———+———+—————-+
3 rows in set (0.01 sec)
MariaDB [administracion]> alter table usuario1 add foreign key(nick_usuario) references usuario(nick_usu
ario);
ERROR 1005 (HY000): Can’t create table `administracion`.`#sql-6f8_d22` (errno: 150 “Foreign key constrai
nt is incorrectly formed”)
谢谢
此致
Victor G.