MySQL ERROR 1005: Unable to create table (errno: 150)

Question

I am trying to create the following table

 create table messaInScena ( data date, ora time, spazio varchar(20), spettacolo varchar(40), postiDisponibili smallint, prezzoIntero decimal(5,2), prezzoRidotto decimal(5,2), prezzoStudenti decimal(5,2), primary key (data, ora, spazio), foreign key (spazio) references spazio(nome) on update cascade on delete set null, foreign key (spettacolo) references spettacolo(titolo) on update cascade on delete set null, constraint RA3_1 check (postiDisponibili >= 0) ) ; 

but I get the following error: Error code: 1005 Unable to create table "teatrosql.messainscena" (errno: 150)

The tables referenced by foreign keys are:

 create table spazio ( nome varchar(20) primary key, indirizzo varchar(40) not null, pianta varchar(20), capienza smallint ); create table spettacolo ( titolo varchar(40) primary key, descrizione LONGBLOB, annoProduzione char(4) ); 

I already checked that fk are unique and that there are no typos (but given the control, you also don't know: D). As you can see, reference fields are primary keys. between fields and types of links and sizes fk match.

Where am I mistaken?

other DB tables

 create table teatro ( nome varchar(20) primary key, telefono varchar(15), fax varchar(15), indirizzo varchar(40) not null, email varchar(30), url varchar(30) ); create table biglietteria ( nome varchar(20) primary key, indirizzo varchar(40) not null, email varchar(30), telefono varchar(15), teatro varchar(20), foreign key (teatro) references teatro(nome) on update cascade on delete set null ); create table orario ( biglietteria varchar(20), giorno varchar(10), inizio time, fine time, primary key(biglietteria, giorno, inizio), foreign key (biglietteria) references biglietteria(nome) on update cascade on delete cascade ); create table notizia ( data date, ora time, oggetto varchar(100), testo LONGBLOB, primary key(data, ora, oggetto) ); create table newsletter ( teatro varchar(20), data date, ora time, oggetto varchar(100), primary key(teatro, data, ora, oggetto), foreign key (teatro) references teatro(nome)on update cascade on delete cascade, foreign key (data, ora, oggetto) references notizia(data, ora, oggetto) on update cascade on delete cascade ); create table dipendente ( cf char(16) primary key, nome varchar(20) not null, cognome varchar(20) not null, dataDiNascita date, luogoDiNascita varchar(20), residenza varchar(30), telefonoFisso varchar(15), telefonoMobile varchar(15), email varchar(30) ); create table lavoro ( teatro varchar(20), dipendente char(16), dataAssunzione date, ruolo varchar(5), cda boolean, primary key(teatro, dipendente), foreign key (teatro) references teatro(nome) on update cascade on delete cascade, foreign key (dipendente) references dipendente(cf) on update cascade on delete cascade, constraint RA1 check ( cda = false or (year(current_date) - year(dataAssunzione) > 10) or (year(current_date) - year(dataAssunzione) = 10 and month(current_date) > month(dataAssunzione)) or (year(current_date) - year(dataAssunzione) = 10 and month(current_date) = month(dataAssunzione) and day(current_date) >= day(dataAssunzione)) ), check (ruolo in ('CA', 'POD', 'CUSRP', 'ACF')) ); create table stipendio ( dipendente char(16), inizio date, importo decimal(6,2), primary key(dipendente, inizio), foreign key (dipendente) references dipendente(cf) on update cascade on delete cascade ) ; create table luogo ( teatro varchar(20), spazio varchar(20), primary key(teatro, spazio), foreign key (teatro) references teatro(nome) on update cascade on delete cascade, foreign key (spazio) references spazio(nome) on update cascade on delete cascade ) ; 

Answer 1

You can check the status of InnoDB ( SHOW ENGINE INNODB STATUS ) to determine the exact cause of the constraint failure. Another option is to add foreign key constraints after creating the table.

In your case, it seems that you are missing the type of engine. Column types must also match. The primary key in the reference tables is most likely NOT NULL , and they are not in messaInScena .

 create table spazio ( nome varchar(20) NOT NULL primary key, indirizzo varchar(40) not null, pianta varchar(20), capienza smallint ) ENGINE=InnoDB; create table spettacolo ( titolo varchar(40) NOT NULL primary key, descrizione LONGBLOB, annoProduzione char(4) ) ENGINE=InnoDB; create table messaInScena ( data date, ora time, spazio varchar(20) NOT NULL, spettacolo varchar(40) NOT NULL, postiDisponibili smallint, prezzoIntero decimal(5,2), prezzoRidotto decimal(5,2), prezzoStudenti decimal(5,2), primary key (data, ora, spazio), foreign key (spazio) references spazio(nome) on update cascade on delete set null, foreign key (spettacolo) references spettacolo(titolo) on update cascade on delete set null, constraint RA3_1 check (postiDisponibili >= 0) ) ENGINE=InnoDB; 

Answer 2

you are brilliant! First of all, I checked the status of InnoDB, and the reason for this was that I tried to set null to remove fk, so I changed the request this way.

 create table messaInScena ( data date, ora time, spazio varchar(20), spettacolo varchar(40), postiDisponibili smallint, prezzoIntero decimal(5,2), prezzoRidotto decimal(5,2), prezzoStudenti decimal(5,2), primary key (data, ora, spazio), foreign key (spazio) references spazio(nome) on update cascade on delete cascade, foreign key (spettacolo) references spettacolo(titolo) on update cascade on delete cascade, constraint RA3_1 check (postiDisponibili >= 0) ) ; 

and execute it. now it works. anyway, now i will transfer the changes that you suggested to me