MariaDB: InnoDB foreign key constraint errors
Introduction
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:
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;
As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:
- InnoDB (both innodb_plugin and 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/ and http://scaledb.com/pdfs/TechnicalOverview.pdf)
- MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)
MariaDB foreign key syntax is documented at https://mariadb.com/kb/en/mariadb/foreign-keys/ (and MySQL at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).
However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.
Constraint name not unique
Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:
-------------- 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)
These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:
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.
In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
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)
No index
There should be an index for columns in a referenced table that contains referenced columns as the first columns.
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)
Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:
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 http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
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)
Referenced table not found
A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:
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)
Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
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).
Temporary tables
Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.
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)
These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:
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)
Column count does not match
There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:
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)
The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:
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)
Incorrect cascading
A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:
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.
Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 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)
Incorrect types
Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:
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 http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:
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)
Conclusions
There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!
References
I believe you mean “Temporary Tables” (tables that exist for a short time) as opposed to “Temporal tables” (tables that have to do with time).
You are correct, I will fix the error.
Thank you so much for the good article, Jan!
You wrote: Column count does not match.
alter table t1 add foreign key(a,b) references t1(a)
ERROR 1005 (HY000): Can’t create table ‘test.#sql-4856_1’ (errno: 150)
This is bug? Syntax error must be determined when the ALTER TABLE clause is parsed.
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
As you can see there is different number of columns in foreign key definition and references definition. Strictly speaking this could be checked during syntax parsing but as MariaDB/MySQL does not fully support foreign keys (for all storage engines) InnoDB does internal parsing for foreign keys.
Hi Jane this is Victor an I have some similar I would like to see if you can check the below code please: in order to know if I can do these escenario:
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”)
Thanks
Regards
Victor G.