MySQL: ERROR 1215 (HY000): no se puede agregar restricción de key externa

He leído Conceptos del sistema de database , 6ª edición, Silberschatz . Voy a implementar el sistema de database de la universidad que se muestra en el capítulo 2 sobre OS X en MySQL. Pero tengo problemas para crear el course la table. el department la table se ve como

 mysql> select * from department -> ; +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+-----------+ | Biology | Watson | 90000.00 | | Comp. Sci. | Taylor | 100000.00 | | Elec. Eng. | Taylor | 85000.00 | | Finance | Painter | 120000.00 | | History | Painter | 50000.00 | | Music | Packard | 80000.00 | | Physics | Watson | 70000.00 | +------------+----------+-----------+ mysql> show columns from department -> ; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | dept_name | varchar(20) | NO | PRI | | | | building | varchar(15) | YES | | NULL | | | budget | decimal(12,2) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+ 

La creación del course tabla provoca el siguiente error.

 mysql> create table course -> (course_id varchar(7), -> title varchar (50), -> dept_name varchar(20), -> cnetworkingits numeric(2,0), -> primary key(course_id), -> foreign key (dept_name) references department); ERROR 1215 (HY000): Cannot add foreign key constraint 

después de search restricciones de key foránea en google, acabo de enterarme de que la palabra "restricción de key externa" indica que los datos de la columna de key externa en el course tabla deben existir en la columna de key primaria en el department tabla. Pero debería haber encontrado este error al insert datos.

Si no, ¿por qué el autor me hace ejecutar esa statement SQL?

Si realmente ejecuto una statement SQL errónea, ¿debo designar dept_name en la tabla de curso como key externa después de insert algunos datos?

EDITAR : escribiendo set foreign_key_checks=0 en mysql> no corrige el error.

 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course: foreign key (dept_name) references department): Syntax error close to: ) mysql> set foreign_key_checks=0 -> ; Query OK, 0 rows affected (0.00 sec) mysql> create table course -> (course_id varchar(7), -> title varchar(50), -> dept_name varchar(20), -> cnetworkingits numeric(2,0), -> primary key(course_id), -> foreign key (dept_name) references department); ERROR 1215 (HY000): Cannot add foreign key constraint 

La syntax de FOREIGN KEY para CREATE TABLE se estructura de la siguiente manera:

 FOREIGN KEY (index_col_name) REFERENCES table_name (index_col_name,...) 

Entonces su MySQL DDL debería ser:

  create table course ( course_id varchar(7), title varchar(50), dept_name varchar(20), cnetworkingits numeric(2 , 0 ), primary key (course_id), FOREIGN KEY (dept_name) REFERENCES department (dept_name) ); 

Además, en la tabla de department , dept_name debe ser VARCHAR(20)

Se puede encontrar más información en la documentation de MySQL

Cuando recibe este vago post de error, puede encontrar el error más específico al ejecutar

 SHOW ENGINE INNODB STATUS; 

Las razones más comunes son que al crear una key externa, tanto el campo al que se hace reference como el campo de la key externa deben coincidir:

  • El motor debería ser el mismo, por ejemplo, InnoDB
  • El tipo de datos debe ser el mismo y con la misma longitud.
    por ejemplo, VARCHAR (20) o INT (10) NO FIRMADO
  • La intercalación debería ser la misma. por ej. utf8
  • Único : la key externa debe hacer reference al campo que es único (generalmente privado) en la tabla de reference.

Otra causa de este error es:
Ha definido una condición SET NULL aunque algunas de las columnas se definen como NOT NULL.

Tal vez sus columnas de nombre de dept_name tengan sets de caracteres diferentes.

Podría intentar modificar uno o ambos:

 ALTER TABLE department MODIFY dept_name VARCHAR(20) CHARACTER SET utf8; ALTER TABLE course MODIFY dept_name VARCHAR(20) CHARACTER SET utf8; 
 foreign key (dept_name) references department 

Esta syntax no es válida para MySQL. En su lugar debería ser:

 foreign key (dept_name) references department(dept_name) 

MySQL requiere dept_name para ser utilizado dos veces . Una vez para definir la columna extranjera, y una vez para definir la columna principal.

13.1.17.2. Usar Restricciones de la CLAVE EXTRAÑA

… [la] syntax esencial para una definición de restricción de key externa en una CREATE TABLE o ALTER TABLE tiene este aspecto:

 [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION 

También es posible get este error si la key externa no es una key principal dentro de su propia tabla.

Hice una ALTER TABLE y accidentalmente eliminé el estado de la key principal de una columna, y obtuve este error.

Vale la pena señalar que este error también puede ocurrir si la tabla o columna de destino que está utilizando en la parte de REFERENCIAS simplemente no existe.

Solo agrega 'unsigned' para la restricción FOREIGN

 `FK` int(11) unsigned DEFAULT NULL, 

ERROR 1215 (HY000): no se puede agregar restricción de key externa

También vale la pena señalar que obtiene este error cuando el tipo de la columna que es una key externa en otro capaz no coincide explícitamente con la columna en la tabla correcta.

Por ejemplo:

 alter table schoolPersons add index FKEF5AB5E532C8FBFA (student_id), add constraint FKEF5AB5E532C8FBFA foreign key (student_id) references student (id); ERROR 1215 (HY000): Cannot add foreign key constraint 

Esto se debió a que el campo student_id se definió como:

 mysql> desc schoolPersons; +--------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+---------+----------------+ | student_id | bigint(20) | YES | | NULL | | 

mientras que el campo de id en la tabla de student se definió como:

 mysql> desc persons; +--------------+----------------------+------+-----+-------------------+-----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+-------------------+-----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | 

El bigint(20) (generado desde Java por hibernación) no es compatible con int(10) unsigned (Java int ).