Cómo definir llaves primarias y foráneas en MySQL

Cuando diseñamos el esquema relacional de una base de datos —incluso comenzando en una hoja de cálculo en Excel para luego migrarla a MySQL— surgen dudas frecuentes sobre la sintaxis DDL correcta. Recientemente estuve organizando la estructura de un catálogo técnico de motores y enfrenté el reto de definir llaves primarias, relaciones foráneas e índices, además de descifrar por qué el código generado automáticamente por phpMyAdmin se ve tan diferente a un script escrito a mano.

1. Ubicación de la llave primaria en CREATE TABLE

Al definir una tabla en SQL, la forma más limpia de declarar la llave primaria (PRIMARY KEY) es agregarla al final de la lista de columnas como una restricción de tabla (table constraint), dentro del mismo bloque CREATE TABLE.

Por ejemplo, para una tabla de catálogo de distribuciones de motor (motor_distributions), la estructura correcta en MySQL se define así:

CREATE TABLE motor_distributions (
    idnumber BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(250),
    status INT NOT NULL,
    PRIMARY KEY (idnumber)
);

2. Modelando relaciones complejas con claves foráneas e índices

A medida que la base de datos crece, una tabla principal suele vincularse con múltiples tablas maestras o de referencia. En mi modelo, la tabla de especificaciones principales (motors) requería conectarse con 10 tablas de catálogo (configuración, ciclo, distribución, sistema de refrigeración, lubricación, sistema de combustible, tipo de alimentación, encendido, arranque y embrague).

Para garantizar la integridad referencial y optimizar las búsquedas o consultas con JOIN, es necesario incluir tanto las restricciones de clave foránea (FOREIGN KEY) como los índices (INDEX) sobre cada campo de relación.

CREATE TABLE motors (
    idnumber BIGINT NOT NULL,
    configuration BIGINT NOT NULL,
    cycle BIGINT NOT NULL,
    distribution BIGINT NOT NULL,
    valves_per_cylinder TINYINT NOT NULL,
    cooling_system_idnumber BIGINT NOT NULL,
    displacement_cc DECIMAL(6,2) NOT NULL,
    bore_mm DECIMAL(5,2) NOT NULL,
    stroke_mm DECIMAL(5,2) NOT NULL,
    compression_ratio VARCHAR(10) NOT NULL,
    lubrication BIGINT NOT NULL,
    fuel_system BIGINT NOT NULL,
    fuel_delivery_type BIGINT NOT NULL,
    ignition_system BIGINT NOT NULL,
    starter_system BIGINT NOT NULL,
    max_power_hp DECIMAL(5,2) NOT NULL,
    max_power_rpm INT NOT NULL,
    max_torque_nm DECIMAL(5,2) NOT NULL,
    max_torque_rpm INT NOT NULL,
    clutch_type BIGINT NOT NULL,
    description VARCHAR(250),
    observation VARCHAR(250),
    status INT NOT NULL,
    PRIMARY KEY (idnumber),

    -- Claves foráneas
    FOREIGN KEY (configuration) REFERENCES motor_configurations(idnumber),
    FOREIGN KEY (cycle) REFERENCES motor_cycles(idnumber),
    FOREIGN KEY (distribution) REFERENCES motor_distributions(idnumber),
    FOREIGN KEY (cooling_system_idnumber) REFERENCES motor_cooling_systems(idnumber),
    FOREIGN KEY (lubrication) REFERENCES motor_lubrication_systems(idnumber),
    FOREIGN KEY (fuel_system) REFERENCES motor_fuel_systems(idnumber),
    FOREIGN KEY (fuel_delivery_type) REFERENCES motor_fuel_delivery_types(idnumber),
    FOREIGN KEY (ignition_system) REFERENCES motor_ignition_systems(idnumber),
    FOREIGN KEY (starter_system) REFERENCES motor_starter_systems(idnumber),
    FOREIGN KEY (clutch_type) REFERENCES motor_clutch_types(idnumber),

    -- Índices
    INDEX idx_configuration (configuration),
    INDEX idx_cycle (cycle),
    INDEX idx_distribution (distribution),
    INDEX idx_cooling_system (cooling_system_idnumber),
    INDEX idx_lubrication (lubrication),
    INDEX idx_fuel_system (fuel_system),
    INDEX idx_fuel_delivery_type (fuel_delivery_type),
    INDEX idx_ignition_system (ignition_system),
    INDEX idx_starter_system (starter_system),
    INDEX idx_clutch_type (clutch_type)
) ENGINE=InnoDB;

3. El dilema: ¿Por qué el volcado de phpMyAdmin se ve distinto?

Al exportar un volcado de base de datos desde phpMyAdmin, noté que la estructura del código SQL cambia drásticamente respecto a mi script manual. phpMyAdmin crea primero todas las tablas únicamente con sus columnas y tipos de datos, y posteriormente ejecuta comandos ALTER TABLE para agregar los índices y restricciones al final del archivo.

Ambos métodos son totalmente equivalentes y producen exatamente la misma estructura interna en el motor de almacenamiento InnoDB:

• Sintaxis inline (dentro de CREATE TABLE): Ideal cuando diseñas la base de datos manualmente desde cero. Es más legible y concentrada en un solo bloque.
• Sintaxis diferida (CREATE TABLE + ALTER TABLE): Usada por phpMyAdmin, mysqldump y herramientas de migración para asegurar compatibilidad y flexibilidad en backups.

4. Conceptos clave: KEY vs INDEX y restricciones nominadas

¿Es lo mismo KEY que INDEX?

Sí. En MySQL, la palabra clave KEY es un sinónimo directo de INDEX. Ambas crean un índice secundario para optimizar consultas. Las variantes principales son:

• PRIMARY KEY: Identificador único y principal de la tabla.
• UNIQUE KEY / UNIQUE INDEX: Índice que exige que los valores del campo no se repitan.
• KEY / INDEX: Índice convencional para mejorar la velocidad en búsquedas y joins.

Nombres explícitos en restricciones de claves foráneas

Cuando defines una clave foránea directamente como FOREIGN KEY (columna) REFERENCES tabla(id), MySQL asigna de forma transparente un nombre automático a la restricción (por ejemplo, motors_ibfk_1).

Si deseas tener control total para modificar o eliminar esa restricción en el futuro mediante scripts DDL, es recomendable nombrarla explícitamente usando la cláusula CONSTRAINT:

ALTER TABLE motorcycles
  ADD CONSTRAINT fk_motorcycles_manufacturer
  FOREIGN KEY (manufacturer) REFERENCES manufacturers (idnumber);