MySQL ERROR 1005: 无法创建表 (errno: 150)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17812616/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL ERROR 1005: Can't create table (errno: 150)
提问by user2610920
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 Can not create table 'teatrosql.messainscena' (errno: 150)
但我收到以下错误：错误代码：1005 无法创建表 'teatrosql.messainscena' (errno: 150)
The tables that are 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 have already verified that the fk are unique and that there are no typos (but given a control also you that you never know :D). As you can see the reference fields are primary keys. between fields and fk reference types and dimensions coincide ..
我已经证实 fk 是独一无二的，并且没有错别字（但也给了你一个你永远不知道的控制权：D）。如您所见，引用字段是主键。字段和 fk 引用类型和维度之间重合..
where am I wrong??
我哪里错了？？
the the other tables of DB
DB的其他表
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
) ;
回答by Kermit
You can check the status of InnoDB (SHOW ENGINE INNODB STATUS) to determine the exact reason why the constraints are failing. The other option is to add the foreign key constraints after creating the table.
您可以检查 InnoDB ( SHOW ENGINE INNODB STATUS)的状态以确定约束失败的确切原因。另一种选择是在创建表后添加外键约束。
In your case, it appears that you're missing the engine type. The column types must also match. The primary key's on the referenced tables are most likely NOT NULL, and they are not so in messaInScena.
在您的情况下，您似乎缺少引擎类型。列类型也必须匹配。引用表上的主键很可能是NOT NULL，而在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;
回答by user2610920
you are a genious! first of all I checked the status of InnoDB, and the reason of the proble was that i tried to set null the fk on delete so i changed the query in this way
你真是个天才！首先我检查了 InnoDB 的状态，问题的原因是我试图在删除时将 fk 设置为 null，所以我以这种方式更改了查询
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's work. anyway now I'll carry the changes that you suggest me
并执行它。现在开始工作了。无论如何现在我会进行你建议我的改变
