How do I reference a foreign key to serial datatype

Question

I tried to create the following tables in MySQL:

CREATE TABLE IF NOT EXISTS cliente(
    id_cliente SERIAL PRIMARY KEY,
    nombre_cliente VARCHAR(20) NOT NULL,
    direccion_cliente VARCHAR(40)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS archivo(
    id_archivo SERIAL PRIMARY KEY,
    nombre_archivo VARCHAR(30),
    id_cliente INTEGER
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fecha(
    id_fecha INTEGER PRIMARY KEY,
    fk_cliente INTEGER,
    id_archivo INTEGER,
    FOREIGN KEY(fk_cliente) REFERENCES cliente(id_cliente)
)ENGINE=InnoDB;

But, I get the error:

Error Code: 1005. Can't create table 'adm_bordados.fecha' (errno: 150)

The last table fetcha is not created.

Answer 1

Both columns of the foreign key must be of the same type. Since SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE (according to the MySQL Type Overview), you will have to change the type of fecha.fk_cliente. Changing the last statement to this should do it for you:

CREATE TABLE IF NOT EXISTS fecha(
    id_fecha INTEGER PRIMARY KEY,
    fk_cliente BIGINT UNSIGNED,
    id_archivo INTEGER,
    FOREIGN KEY(fk_cliente) REFERENCES cliente(id_cliente)
)ENGINE=InnoDB;