＃1005 - 无法创建表'classorganizer.turma'（错误号：150）

I keep getting the following error: *#1005 - Can't create table 'classorganizer.turma' (errno: 150) (Detalhes...) *

我不断收到以下错误:*#1005 - 无法创建表'classorganizer.turma'(错误号:150)(Detalhes ...)*

from trying to create the table Turma although I've double checked all the foreign keys cases in that class. Does anyone knows whats wrong?

从试图创建表Turma虽然我已经仔细检查了该类中的所有外键案例。有谁知道什么是错的?

Thank you!

CREATE TABLE Usuario(
email VARCHAR(50) NOT NULL,
nome VARCHAR(30),
senha INTEGER NOT NULL,
dataCadastro DATE NOT NULL,

CONSTRAINT pkUsu PRIMARY KEY(email),
CONSTRAINT formatoEmail CHECK(email LIKE '%@%.%')
)ENGINE=InnoDB;

CREATE TABLE Professor(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(30) NOT NULL UNIQUE,
ranking INTEGER DEFAULT 3,
usuario VARCHAR(50) NOT NULL,

CONSTRAINT pk_prof PRIMARY KEY (id),
CONSTRAINT fk_usu FOREIGN KEY (usuario) REFERENCES Usuario(email) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT limites_rank CHECK(ranking >0 AND ranking<6)
)ENGINE=InnoDB;

CREATE TABLE Materia(
codigo VARCHAR(8) NOT NULL,
nro_turmas INTEGER DEFAULT 0,
nome VARCHAR(20) NOT NULL UNIQUE,
nro_cred_aula INTEGER DEFAULT 0,
nro_cred_trab INTEGER DEFAULT 0,
prioridade INTEGER DEFAULT 3,
usuario VARCHAR(50) NOT NULL,

CONSTRAINT pk_prof PRIMARY KEY (id),
CONSTRAINT fk_usu FOREIGN KEY (usuario) REFERENCES Usuario(email) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT limites_prio CHECK(prioridade >0 AND prioridade<6)
)ENGINE=InnoDB;

CREATE TABLE Turma(
nro INTEGER NOT NULL AUTO_INCREMENT,
prioridade INTEGER DEFAULT 3,
materia VARCHAR(8) NOT NULL,
professor INTEGER NOT NULL,
usuario VARCHAR(50) NOT NULL,

CONSTRAINT pk_turma PRIMARY KEY (nro),
CONSTRAINT fk_mat FOREIGN KEY (materia) REFERENCES Materia(codigo) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_prof FOREIGN KEY (professor) REFERENCES Professor(id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT fk_usu FOREIGN KEY (usuario) REFERENCES Usuario(email) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT limites_prio CHECK(prioridade >0 AND prioridade<6)
)ENGINE=InnoDB;

1 个解决方案

#1

There are lots of errors.

有很多错误。

CONSTRAINT pk_prof PRIMARY KEY (id). id column doesn't exist in table Materia.

CONSTRAINT pk_prof PRIMARY KEY(id)。表Matera中不存在id列。

Constraint names (pk_prof, fk_usu) are already used in Professor table. You have used it in both Materia and Turma table. Correct it by choosing unique names. Easiest way is to omit the name. MySQL handle it.

约束名称(pk_prof,fk_usu)已在教授表中使用。您已在Materia和Turma表中使用过它。通过选择唯一的名称来纠正它。最简单的方法是省略名称。 MySQL处理它。

codigo column of Materia is referenced in Turma table, But its not a *key.*

Materia的codigo列在Turma表中引用,但它不是*键。*

professor column of Turma is defined to be NOT NULL. But you used ON DELETE SET NULL.

Turma的教授列定义为NOT NULL。但是你使用了ON DELETE SET NULL。

#1