incorrectly - La clave externa no funciona en MySQL: ¿Por qué puedo INSERTAR un valor que no está en la columna externa?

primary key and foreign key

Question

Creé una tabla en MySQL:

CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT,
type ENUM(''rate'',''report'',''submit'',''edit'',''delete'') NOT NULL,
Q_id int NOT NULL,
U_id int NOT NULL,
date DATE NOT NULL,
time TIME NOT NULL,
rate tinyint(1),
PRIMARY KEY (A_id),
CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id));

Esto creó la tabla que quería muy bien (aunque el comando "DESCRIBE acciones" me mostró que las claves externas eran claves de tipo MUL, y no estoy seguro de lo que esto significa). Sin embargo, cuando trato de ingresar un Q_id o un U_id que no existe en las tablas de preguntas o usuarios, MySQL aún permite estos valores.

¿Qué hice mal? ¿Cómo puedo evitar que una tabla con una clave externa acepte datos no válidos?

ACTUALIZACIÓN 1

Si agrego TYPE=InnoDB al final, aparece un error:

ERROR 1005 (HY000): No se puede crear la tabla ''./quotes/actions.frm'' (errno: 150)

¿Por qué podría suceder eso?

ACTUALIZACIÓN 2

Me dijeron que es importante hacer cumplir la integridad de los datos con claves externas funcionales, pero también que InnoDB no debe usarse con MySQL. ¿Que recomiendas?

Answer 1

Como se señaló, su tabla tiene que ser InnoDB para las restricciones de FK que se aplicarán.

Solo me encontré con la ''No se puede crear la tabla'' en el caso en que intento crear una restricción de clave externa donde mi columna local es de un tipo diferente de la columna externa.

Answer 2

Encontré el siguiente artículo. No tengo tiempo para probarlo, actualmente, pero puede ser útil:

http://forums.mysql.com/read.php?22,19755,43805

El autor, Edwin Dando, dice:

ambas tablas deben ser INNODB. El campo de la clave externa debe tener un índice. El campo clave de referencia y el campo al que se hace referencia deben ser del mismo tipo (solo uso enteros) y, después de horas de dolor, deben estar DESIGNADOS.

Answer 3

el problema es que las preguntas.p_id y users.p_id no están definidas como INT NOT NULL. para que las claves externas funcionen, la definición de las columnas en ambos lados de la clave externa debe coincidir exactamente, con la excepción de auto_increment y por defecto.

Answer 4

Sé que este hilo se abrió hace mucho tiempo, pero estoy publicando este mensaje para futuros usuarios que buscarán la respuesta. Estaba teniendo el mismo problema con la clave externa en mysql. Lo siguiente funcionó para mí.

Tabla de padres:

CREATE TABLE NameSubject (
  Autonumber INT NOT NULL AUTO_INCREMENT,
  NameorSubject nvarchar(255),
  PRIMARY KEY (Autonumber)
 ) ENGINE=InnoDB;

Tabla de niños:

CREATE TABLE Volumes (
  Autonumber INT NOT NULL,
  Volume INT,
  Pages nvarchar(50),
  Reel int,
  Illustrations bit,
  SSMA_TimeStamp timestamp,
  Foreign KEY (Autonumber) references NameSubject(Autonumber)
  ON  update cascade 
)engine=innodb;

"ON update cascade" hizo la magia para mí.

Espero que esto funcione para otros. La mejor de las suertes.

Answer 5

Solo para evitar las horas de dolor de cabeza que he pasado, mientras toca giraffa, asegúrese de que @FOREIGN_KEY_CHECKS esté configurado en 1.

SELECCIONE @@ FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS = 1

Answer 6

Creo que algunas de las personas que tienen este problema podrían estar comenzando con algunas de las bases de datos de ejemplo que se proporcionan en el sitio web de ORACLE para MYSQL (por ejemplo, sakila DB). No se olvide de "activar de nuevo las restricciones de clave externa" al final de su script (por ejemplo, al comienzo del script de DB de sakila se desactivan)

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''TRADITIONAL'';

crea tus tablas aquí

entonces no olvides esto:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 7

Supongo que su motor de almacenamiento predeterminado es MyISAM, que ignora las restricciones de clave externa. Acepta silenciosamente la declaración de una clave foránea, pero no almacena la restricción ni la impone posteriormente.

Sin embargo, crea implícitamente un índice en las columnas que ha declarado para la clave externa. En MySQL, " KEY " es un sinónimo de " INDEX ". Eso es lo que se muestra en la salida DESCRIBE: un índice, pero no una restricción.

Ahora puede insertar valores no válidos en la tabla porque no hay restricciones. Para obtener una restricción que refuerce la integridad referencial, debe usar el motor de almacenamiento InnoDB:

CREATE TABLE actions (
  A_id int NOT NULL AUTO_INCREMENT,
  ...
  CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
  CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id)
) ENGINE=InnoDB;

Siempre he pensado que fue un gran error por parte de MySQL ignorar silenciosamente las declaraciones de restricciones de claves foráneas. No hay ningún error o advertencia de que el motor de almacenamiento no los admite.

Lo mismo es cierto para las restricciones CHECK, por cierto. Ningún motor de almacenamiento utilizado con MySQL admite restricciones CHECK, pero el analizador SQL lo acepta sin ninguna queja.

El problema errno 150 ocurre cuando no puede crear la tabla InnoDB, porque no tiene sentido la restricción de clave externa. Puede obtener más información con:

SHOW ENGINE INNODB STATUS;

Algunos requisitos para las claves externas de InnoDB:

• La tabla referenciada también debe ser InnoDB.
• La tabla a la que se hace referencia debe tener un índice y una clave principal.
• Los tipos de datos SQL de la columna FK y la columna PK referenciada deben ser idénticos. Por ejemplo, INT no coincide con BIGINT o INT UNSIGNED.

Puede cambiar el motor de almacenamiento de una tabla que contiene datos:

ALTER TABLE actions ENGINE=InnoDB;

Esto copia efectivamente toda la tabla MyISAM en una tabla InnoDB, luego, una vez que tiene éxito, descarta la tabla MyISAM y cambia el nombre de la nueva tabla InnoDB por el nombre de la anterior tabla MyISAM. Esto se denomina "reestructuración de la tabla" y puede llevar mucho tiempo, dependiendo de la cantidad de datos que hay en la tabla. Se produce una reestructuración de la tabla durante ALTER TABLE, incluso en algunos casos donde puede parecer innecesario.

Re su actualización 2:

Me dijeron que es importante hacer cumplir la integridad de los datos con claves externas funcionales, pero también que InnoDB no debe usarse con MySQL. ¿Que recomiendas?

Usted lo dijo? Es absolutamente falso. InnoDB tiene mejor rendimiento que MyISAM (aunque InnoDB necesita más atención para ajustar la configuración ), InnoDB admite cambios atómicos, transacciones, claves externas, e InnoDB es mucho más resistente a la corrupción de datos en un crash.

A menos que esté ejecutando una versión anterior, no compatible de MySQL (5.0 o anterior), debe utilizar InnoDB como su opción de motor de almacenamiento predeterminado , y usar MyISAM solo si puede demostrar una carga de trabajo específica que se beneficia de MyISAM.

Answer 8

Para aquellos que todavía tienen problemas con mysql haciendo caso omiso de las restricciones de claves externas y para aquellos que las respuestas anteriores o en cualquier otra pregunta relacionada no resolvieron su rompecabezas, aquí está lo que encontré ser el problema.

Si declara sus claves foráneas como tales

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

Entonces parece que se ignora la clave foránea, para imponer la restricción sin (aparentemente) tener que usar cualquiera de los comandos SET, use la siguiente declaración.

id  INTEGER UNSIGNED,
CONSTRAINT fk_id  FOREIGN KEY (id)  REFERENCES A_Table(id)

De esta manera resolvió el problema para mí. No estoy seguro de por qué, ya que muchos dicen que la primera declaración es solo una abreviatura de la segunda variante.

Answer 9

Esta respuesta me hubiera ahorrado mucho tiempo si lo hubiera visto primero. Prueba los siguientes tres pasos, que he ordenado por frecuencia de errores de principiante:

(1) Cambie la tabla para que sea InnodDB agregando "ENGINE = InnoDB" a sus instrucciones "CREATE TABLE".

Otros motores, que pueden ser los predeterminados, no admiten restricciones de clave externa, pero tampoco arrojan un error o advertencia diciéndole que no son compatibles.

(2) Asegúrese de que las restricciones de clave externa se estén verificando ejecutando "SET foreign_key_checks = ''ON''"

(3) Agregue "ON ACTUALIZACIÓN CASCADA" a su declaración de clave externa.

Nota: asegúrese de que el comportamiento en cascada sea el que desea. Hay otras opciones ...

Answer 10

Bueno, supongo que de alguna manera está marcada la opción "Saltar la creación de FORIEN KEYS", puede suceder en la sección de "opciones" del proceso "Forward Engineering".

Answer 11

Foreign KEY (autonumber) hace referencia a NameSubject (Autonumérico) ON cascada de actualización) engine = innodb;

yo también he usado la cascada de actualización ON .... funciona

Restricciones de InnoDB y KEY FOREIGN Esta sección describe las diferencias en el manejo de las claves externas del motor de almacenamiento InnoDB en comparación con el del servidor MySQL.

Definiciones de clave foránea Las definiciones de clave foránea para tablas InnoDB están sujetas a las siguientes condiciones:

InnoDB permite que una clave externa haga referencia a cualquier columna de índice o grupo de columnas. Sin embargo, en la tabla a la que se hace referencia, debe haber un índice donde las columnas a las que se hace referencia estén enumeradas como las primeras columnas en el mismo orden.

InnoDB no admite actualmente claves foráneas para tablas con particiones definidas por el usuario. Esto significa que ninguna tabla InnoDB particionada por el usuario puede contener referencias de clave externa o columnas referenciadas por claves externas.

InnoDB permite una restricción de clave externa para hacer referencia a una clave no única. Esta es una extensión InnoDB para SQL estándar.

Acciones referenciales Las acciones referenciales para claves externas de tablas InnoDB están sujetas a las siguientes condiciones:

Mientras SETDFAULT está permitido por el servidor MySQL, InnoDB lo rechaza como no válido. Las sentencias CREATE TABLE y ALTER TABLE que utilizan esta cláusula no están permitidas para tablas InnoDB.

Si hay varias filas en la tabla principal que tienen el mismo valor de clave referenciado, InnoDB actúa en las comprobaciones de clave externa como si las otras filas primarias con el mismo valor de clave no existieran. Por ejemplo, si ha definido una restricción de tipo RESTRICT, y hay una fila secundaria con varias filas principales, InnoDB no permite la eliminación de ninguna de esas filas principales.

InnoDB realiza operaciones en cascada a través de un algoritmo de profundidad inicial, basado en registros en los índices correspondientes a las restricciones de clave externa.

Si ON UPDATE CASCADE o ON UPDATE SET NULL recurses para actualizar la misma tabla que ha actualizado previamente durante la cascada, actúa como RESTRICT. Esto significa que no puede usar las operaciones autorreferenciales ON UPDATE CASCADE o ON UPDATE SET NULL. Esto es para evitar bucles infinitos resultantes de actualizaciones en cascada. Un autorreferencial ON DELETE SET NULL, por otro lado, es posible, como lo es un autorreferencial ON DELETE CASCADE. Las operaciones en cascada no pueden anidarse a más de 15 niveles de profundidad.

Al igual que MySQL en general, en una instrucción SQL que inserta, elimina o actualiza muchas filas, InnoDB comprueba las restricciones ÚNICA y EXTRAÑA de LÍNEAS fila por fila. Al realizar comprobaciones de claves externas, InnoDB establece bloqueos compartidos a nivel de fila en registros secundarios o secundarios que debe observar. InnoDB comprueba las restricciones de clave externa de inmediato; el cheque no se difiere al compromiso de transacción. De acuerdo con el estándar SQL, el comportamiento predeterminado debe ser la verificación diferida. Es decir, las restricciones solo se verifican después de que se haya procesado toda la declaración SQL. Hasta que InnoDB implemente la verificación de restricciones diferidas, algunas cosas serán imposibles, como eliminar un registro que se refiera a sí mismo utilizando una clave externa.

Información de uso y error de clave externa Puede obtener información general sobre claves externas y su uso al consultar la tabla INFORMATION_SCHEMA.KEY_COLUMN_USAGE. Consulte también la Sección 13.1.17.2, "Uso de restricciones de KEY FOREIGN".

Además de SHOW ERRORS, en el caso de un error de clave externa que involucra tablas InnoDB (generalmente Error 150 en el servidor MySQL), puede obtener una explicación detallada del error de clave externa InnoDB más reciente al verificar el resultado de SHOW ENGINE INNODB STATUS .