USE ejemplo;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS carreras;
CREATE TABLE carreras (
    id INT NOT NULL AUTO_INCREMENT,
    nombre TEXT NOT NULL,
    correo TEXT NOT NULL,
    
    PRIMARY KEY (id),
    UNIQUE (nombre(16))
) ENGINE=InnoDB;

DROP TABLE IF EXISTS alumnos;
CREATE TABLE alumnos (
    matricula INT NOT NULL,
    nombre TEXT NOT NULL,
    apellido TEXT NOT NULL,
    carrera INT NOT NULL,
    estado INT NOT NULL,
    creditos INT NOT NULL,
    
    PRIMARY KEY (matricula),
    INDEX (apellido(16)),
    FOREIGN KEY (carrera) REFERENCES carreras (id)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS telefonos;
CREATE TABLE telefonos (
    matricula INT NOT NULL,
    telefono TEXT NOT NULL,
    
    FOREIGN KEY (matricula) REFERENCES alumnos (matricula) ON DELETE CASCADE
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;