foreign - MySQL terminología "restricciones" vs diferencia "claves extranjeras"?

mysql cannot add foreign key constraint (6)

Estoy mirando los documentos de MySQL aquí y trato de resolver la distinción entre FOREIGN KEYs y CONSTRAINTs. Pensé que un FK era una limitación, pero los documentos parecen hablar de ellos como si fueran cosas separadas.

La sintaxis para crear un FK es (en parte) ...

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...)

Entonces la cláusula "CONSTRAINT" es opcional. ¿Por qué lo incluirías o no incluirías? Si lo deja afuera, ¿crea MySQL una clave externa pero no una restricción? ¿O es más como una "RESTRICCIÓN" no es más que un nombre para ti FK, entonces si no lo especificas obtienes un FK anónimo?

Cualquier aclaración sería muy apreciada.

Gracias,

Ethan

No puedo responder por MySQL pero los FK son restricciones. Cualquier cosa que fuerce tus datos a una determinada condición es una restricción. Hay varios tipos de restricciones: Única, Clave principal, Comprobar y Claves foráneas son todas restricciones. Tal vez MySQL tiene otros.

A veces, las palabras están permitidas en los comandos, pero no son necesarias para la legibilidad, como el FROM en la declaración DELETE.

Sí, una clave externa es un tipo de restricción. MySQL tiene un soporte desigual para las restricciones:

PRIMARY KEY : sí como restricción de tabla y restricción de columna.
FOREIGN KEY : sí como restricción de tabla, pero solo con motores de almacenamiento InnoDB y BDB; de lo contrario, se analizará pero se ignorará.
CHECK : analizado pero ignorado en todos los motores de almacenamiento.
UNIQUE : sí como restricción de tabla y restricción de columna.
NOT NULL : sí como restricción de columna.
DEFERRABLE y otros atributos de restricción: sin soporte.

La cláusula CONSTRAINT permite nombrar la restricción de forma explícita, ya sea para hacer que los metadatos sean más legibles o para usar el nombre cuando desee soltar la restricción. El estándar SQL requiere que la cláusula CONSTRAINT sea opcional. Si lo deja afuera, el RDBMS crea un nombre automáticamente, y el nombre depende de la implementación.

En general (MySQL no necesario), las claves externas son restricciones, pero las restricciones no siempre son claves externas. Piensa en las principales restricciones clave, restricciones únicas, etc.

Volviendo a la pregunta específica, está en lo cierto, omitiendo la parte de CONSTRAINT [símbolo] creará un FK con un nombre generado automáticamente.

Este es probablemente el tema más confuso en MySQL.

Mucha gente dice que, por ejemplo, la "CLAVE PRIMARIA", la "CLAVE EXTRAÑA" y la tecla "ÚNICA" son en realidad índices. (La documentación oficial de MySQL está incluida aquí)

Muchos otros, por otro lado, dicen que son más bien restricciones (lo cual tiene sentido, porque cuando los usas, realmente estás imponiendo restricciones en las columnas afectadas).

Si realmente son índices, ¿qué sentido tiene utilizar la cláusula de restricción para darle un nombre, ya que se supone que usted puede usar el nombre de ese índice cuando lo creó?

Ejemplo:

... FOREIGN KEY index_name (col_name1, col_name2, ...)

Si FOREIGN KEY es un índice, entonces deberíamos poder usar el index_name para manejarlo. Sin embargo, no podemos.

Pero si no son índices sino restricciones reales que usan índices para funcionar, entonces esto tiene sentido.

En cualquier caso, no lo sabemos. En realidad, nadie parece saberlo.

Si no me equivoco, las restricciones necesitan índices, por lo que cuando crea, por ejemplo, una restricción de clave externa, MySQL también crea automáticamente un índice.

A partir de ahora, nuestros DDL de CREATE TABLE tienen este formato: observe la sintaxis de definición KEY UNIQUE KEY y FOREIGN KEY que hemos utilizado.

CREATE TABLE my_dbschema.my_table ( id INT unsigned auto_increment PRIMARY KEY, account_nbr INT NOT NULL, account_name VARCHAR(50) NOT NULL, active_flg CHAR(1) NOT NULL DEFAULT ''Y'', vendor_nbr INT NOT NULL, create_ts TIMESTAMP NOT NULL DEFAULT current_timestamp, create_usr_id VARCHAR(10) NOT NULL DEFAULT ''DFLTUSR'', last_upd_ts TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp, last_upd_usr_id VARCHAR(10) NOT NULL DEFAULT ''DFLTUSR'', UNIQUE KEY uk1_my_table(account_nbr, account_name), FOREIGN KEY fk1_my_table(vendor_nbr) REFERENCES vendor(vendor_nbr) );

En este formato, MySQL está creando INDEX-es con los nombres uk1_my_table y fk1_my_table automáticamente; pero el nombre del objeto FK es algo diferente - my_table_ibfk_1 (es decir, tablename_ibfk_N - system defined). Así que ALTER TABLE my_table DROP FOREIGN KEY fk1_my_table no funcionará (y por lo tanto frustrante y provocando alarmas), ya que no hay un objeto FK db con ese nombre.

Aquí hay un formato DDL alternativo con las constarints (Ref: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html ): -

CREATE TABLE my_dbschema.my_table ( id INT unsigned auto_increment PRIMARY KEY, account_nbr INT NOT NULL, account_name VARCHAR(50) NOT NULL, active_flg CHAR(1) NOT NULL DEFAULT ''Y'', vendor_nbr INT NOT NULL, create_ts TIMESTAMP NOT NULL DEFAULT current_timestamp, create_usr_id VARCHAR(10) NOT NULL DEFAULT ''DFLTUSR'', last_upd_ts TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp, last_upd_usr_id VARCHAR(10) NOT NULL DEFAULT ''DFLTUSR'', CONSTRAINT uk1_my_table UNIQUE KEY (account_nbr, account_name), CONSTRAINT fk1_my_table FOREIGN KEY (vendor_nbr) REFERENCES vendor(vendor_nbr) );

En este formato, MySQL sigue creando INDEX-es con los nombres uk1_my_table y fk1_my_table automáticamente, pero el nombre del objeto FK no es algo diferente: es fk1_my_table como se menciona en el DDL. Así que ALTER TABLE my_table DROP FOREIGN KEY fk1_my_table funciona, pero deja el ÍNDICE del mismo nombre.

Y, tenga en cuenta que ALTER TABLE my_table DROP INDEX fk1_my_table no funcionará inicialmente (cuando el FK aún no se ha descartado), con un mensaje de error de que se está utilizando en un FK. Si el comando DROP FK se ha ejecutado con éxito, solo entonces DROP INDEX funciona.

Espero que esto explique y ayude a resolver la confusión.