CODEWITHSUNDEEP
mysqlsqlforeign-keys
Aldana Lucía
Aldana Lucía

Reputation: 3

MySQL - Cannot add foreign key constraint

Please help I'm pretty noob at this and I don't know how I should put the foreign key If anybody can help, I'll appreciate it!

mysql> 
    create table Evento
    (idEven int NOT NULL auto_increment,
    denominacion varchar(20),
    horaInicio time,
    idLugar int,
    cupo int,

    constraint PK_Evento primary key (idEven));

Query OK, 0 rows affected 

mysql> 
    create table Lugar
    (idLugar int NOT NULL,
    nombre varchar(20),
    direccion varchar(20),
    localidad varchar(20),

    constraint PK_Lugar primary key (idLugar),
    constraint FK_Lugar foreign key (idLugar) references Evento(idLugar));

ERROR 1215 (HY000): Cannot add foreign key constraint

Upvotes: 0

Views: 153

Answers (1)

spencer7593
spencer7593

Reputation: 108370

It looks like you want a foreign key from event to reference place.

That is, it looks like an "event" happens at one place, but a place can have many "events". To represent that in the "event" table, we reference the "place" that the event happens. You've already got the column there.

Just switch your thinking around... the foreign key is defined on the child table, and references the parent. The idLugar column in the event table is a reference to a row in Lugar.

Just remove that second foreign key constraint from the Lugar table, and instead, add a constraint to the Evento table.

for example:

 ALTER TABLE `Evento` ADD
   CONSTRAINT FK_Evento_Lugar FOREIGN KEY (idLugar) REFERENCES Lugar(idLugar)

NOTE: Beware of case-sensitivity issues with table names. The pattern we have adopted to avoid problems is to lowercase all table names, and set lower_case_table_names=1 in the my.cnf.

Reference: https://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

Upvotes: 1

Related Questions