erno 150 in MySQL

Question

I cant finish my DB because of this error. It seems to be in the bold line.

create database hospital;

create table medico(
CRM INT NOT NULL,
NomeMedico VARCHAR (40) NOT NULL,
NomeDepto VARCHAR (40),
ChefeDepto VARCHAR (40),
Especialidade1 VARCHAR(40),
Especialidade2 VARCHAR (40),
PRIMARY KEY (CRM)
)ENGINE = INNODB;

create table paciente(
Registro INT NOT NULL,
NomePac VARCHAR (40) NOT NULL,
DataNascimento DATE NOT NULL,
SexoPac ENUM ('M','F') NOT NULL,
Rua VARCHAR (40),
Numero INT,
PRIMARY KEY (Registro)
)ENGINE = INNODB;

create table telefonepaciente(
RegistroCE INT NOT NULL,
Telefone INT NOT NULL,
PRIMARY KEY (RegistroCE, Telefone),
FOREIGN KEY (RegistroCE) REFERENCES paciente (Registro)
)ENGINE = INNODB;

create table andar(
NumAndar TINYINT NOT NULL,
DescAndar TEXT,
PRIMARY KEY (NumAndar)
)ENGINE = INNODB;

create table especialidade(
CodEspec INT NOT NULL,
NomeEspec VARCHAR (40),
PRIMARY KEY (CodEspec)
)ENGINE = INNODB;

create table enfermaria(
NumAndarCE TINYINT NOT NULL,
CodEnfermaria INT NOT NULL,
NomeEnfermaria VARCHAR (40),
SituacaoEnfermaria TEXT,
PRIMARY KEY (NumAndarCE, CodEnfermaria),
FOREIGN KEY (NumAndarCE) REFERENCES andar (NumAndar)
)ENGINE = INNODB;

create table tipo(
CodTipo INT NOT NULL,
NomeTipo VARCHAR (40),
PRIMARY KEY (CodTipo)
)ENGINE = INNODB;

create table leito(
NumLeito INT NOT NULL,
SituacaoLeito TEXT,
CodTipoCE INT NOT NULL,
CodEspecCE INT NOT NULL,
NumAndarCE TINYINT NOT NULL,
CodEnfermariaCE INT NOT NULL,
TipoEspecialidade TEXT,
PRIMARY KEY (NumLeito),
FOREIGN KEY (CodTipoCE) REFERENCES tipo (CodTipo),
FOREIGN KEY (CodEspecCE) REFERENCES especialidade (CodEspec),
FOREIGN KEY (NumAndarCE) REFERENCES andar (NumAndar),
**FOREIGN KEY (CodEnfermariaCE) REFERENCES enfermaria (CodEnfermaria)**
)ENGINE = INNODB;

create table internado(
RegistroCE INT NOT NULL,
NumLeitoCE SMALLINT NOT NULL,
CRMCE INT NOT NULL,
Naturalidade VARCHAR (40),
DataInternacao DATE,
DataAlta DATE,
QtdeDiasInternado MEDIUMINT,
PRIMARY KEY (RegistroCE, NumLeitoCE, CRMCE),
FOREIGN KEY (RegistroCE) REFERENCES paciente (Registro),
FOREIGN KEY (NumLeitoCE) REFERENCES leito (NumLeito),
FOREIGN KEY (CRMCE) REFERENCES medico (CRM)
) ENGINE = INNODB;*

I've removed the SQL Server tag you added, as you specifically state you're using MySQL SQL Server is a specific RDBMS product belonging to Microsoft. Please do not tag spam by adding tags that don't actually apply to your post, and read the tag descriptions before adding them. Tags have meaning and purpose here, and using them incorrectly isn't appropriate. Also, make an effort to format the code in your question; you can find formatting help using the **?** icon on the editor toolbar. When you [edit] to do that, you should also add the **exact** error message you're getting to your post. — Ken White, Jun 12 '20 at 23:25
This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 13 '20 at 00:09
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 13 '20 at 00:10
Does this answer your question? [MySQL: Can't create table (errno: 150)](https://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150) See also [Foreign Keys and MySQL Errors](https://stackoverflow.com/a/1817013/3404097) — philipxy, Jun 13 '20 at 00:13
whats the actual error can you add it ? bold does not work on code. — Vidal, Jun 13 '20 at 02:51

score 0 · Answer 1 · answered Jun 12 '20 at 23:33

Consider this part of your code:

create table leito(
    ...
    FOREIGN KEY (NumAndarCE) REFERENCES andar (NumAndar),
    FOREIGN KEY (CodEnfermariaCE) REFERENCES enfermaria (CodEnfermaria)
)

In the meantime, table enfermaria has:

create table enfermaria(
    ...
    PRIMARY KEY (NumAndarCE, CodEnfermaria),
    FOREIGN KEY (NumAndarCE) REFERENCES andar (NumAndar)
)

You are not referencing this table properly. Instead of two different foreign keys on NumAndar and CodEnfermaria, you need a compound foreign key, that references both primary key columns at once. So:

    create table leito(
    ...
    FOREIGN KEY (NumAndarCE, CodEnfermariaCE) 
        REFERENCES enfermaria(NumAndarCE, CodEnfermaria)
)

Another problem is that column internado(NumLeitoCE) has a different datatype than leito(NumLeito), which it references: you need to change it from SMALLINT to INT.

With these changes, your code runs fine in this db fiddle.

score 0 · Answer 2 · answered Jun 12 '20 at 23:35

You had two small problems ion entermaria you need a KEY (CodEnfermaria)

and in internado NumLeitoCE INT NOT NULL, has to be int

create table medico(
CRM INT NOT NULL,
NomeMedico VARCHAR (40) NOT NULL,
NomeDepto VARCHAR (40),
ChefeDepto VARCHAR (40),
Especialidade1 VARCHAR(40),
Especialidade2 VARCHAR (40),
PRIMARY KEY (CRM)
)ENGINE = INNODB;

create table paciente(
Registro INT NOT NULL,
NomePac VARCHAR (40) NOT NULL,
DataNascimento DATE NOT NULL,
SexoPac ENUM ('M','F') NOT NULL,
Rua VARCHAR (40),
Numero INT,
PRIMARY KEY (Registro)
)ENGINE = INNODB;

create table telefonepaciente(
RegistroCE INT NOT NULL,
Telefone INT NOT NULL,
PRIMARY KEY (RegistroCE, Telefone),
FOREIGN KEY (RegistroCE) REFERENCES paciente (Registro)
)ENGINE = INNODB;

create table andar(
NumAndar TINYINT NOT NULL,
DescAndar TEXT,
PRIMARY KEY (NumAndar)
)ENGINE = INNODB;

create table especialidade(
CodEspec INT NOT NULL,
NomeEspec VARCHAR (40),
PRIMARY KEY (CodEspec)
)ENGINE = INNODB;

create table enfermaria(
NumAndarCE TINYINT NOT NULL,
CodEnfermaria INT NOT NULL,
NomeEnfermaria VARCHAR (40),
SituacaoEnfermaria TEXT,
PRIMARY KEY (NumAndarCE, CodEnfermaria),
KEY (CodEnfermaria),
FOREIGN KEY (NumAndarCE) REFERENCES andar (NumAndar)
)ENGINE = INNODB;

create table tipo(
CodTipo INT NOT NULL,
NomeTipo VARCHAR (40),
PRIMARY KEY (CodTipo)
)ENGINE = INNODB;

create table leito(
NumLeito INT NOT NULL,
SituacaoLeito TEXT,
CodTipoCE INT NOT NULL,
CodEspecCE INT NOT NULL,
NumAndarCE TINYINT NOT NULL,
CodEnfermariaCE INT NOT NULL,
TipoEspecialidade TEXT,
PRIMARY KEY (NumLeito),
FOREIGN KEY (CodTipoCE) REFERENCES tipo (CodTipo),
FOREIGN KEY (CodEspecCE) REFERENCES especialidade (CodEspec),
FOREIGN KEY (NumAndarCE) REFERENCES andar (NumAndar),
FOREIGN KEY (CodEnfermariaCE) REFERENCES enfermaria (CodEnfermaria)
)ENGINE = INNODB;

create table internado(
RegistroCE INT NOT NULL,
NumLeitoCE INT NOT NULL,
CRMCE INT NOT NULL,
Naturalidade VARCHAR (40),
DataInternacao DATE,
DataAlta DATE,
QtdeDiasInternado MEDIUMINT,
PRIMARY KEY (RegistroCE, NumLeitoCE, CRMCE),
FOREIGN KEY (RegistroCE) REFERENCES paciente (Registro),
FOREIGN KEY (NumLeitoCE) REFERENCES leito (NumLeito),
FOREIGN KEY (CRMCE) REFERENCES medico (CRM)
) ENGINE = INNODB;

erno 150 in MySQL

2 Answers2