Mysql Error 1215: Cannot add Foreign key Constraint

Asked

Viewed 943 times

-3

-- 1 --
create database clinica;
use clinica;

-- 2 --
create table ambulatorio(
idnroa int(15) unsigned not null auto_increment,
andar int(15) not null,
capacidade smallint(45) not null,
primary key (idnroa)
);

create table medicos(
idcodm int(15) unsigned not null auto_increment,
idnroa int(15) unsigned not null ,
nome varchar(40) not null,
idade smallint(15) not null,
especialidade char(20)  not null,
cpf numeric(11) not null,
cidade varchar(30) not null,
nroa int(15) not null, 
primary key (idcodm),
foreign key (idnroa) references ambulatorio (idnroa)
);

create table pacientes(
idcodp int(15) unsigned not null auto_increment,
nome varchar(40) not null,
idade smallint(15) not null,
cidade char(30) not null,
cpf numeric(11) not null,
primary key (idcodp)
);

create table funcionarios(
idcodf int(15) unsigned not null auto_increment,
nome varchar(40) not null,
idade smallint (15) not null,
cpf numeric(11) not null,
cidade varchar(30) not null,
salario numeric(10) not null,
primary key (idcodf)
);

create table consultas(
data date not null,
hora time not null,
idcodm int(15)  not null,
idcodp int(15)  not null,
foreign key (idcodm) references medicos (idcodm),
foreign key (idcodp) references pacientes (idcodp)
);
mysql

  • Do you have any?

  • Always put the COMPLETE ERROR, so it facilitates searches.

  • Why don’t you use Workbench to create the tables and keys, I don’t see the CONSTRAINT, it seems that tried to make in hand the modeling.

  • @clear you tested my answer? Because I ran the full command and ran error-free in my bank. If it works, accept the answer as valid.

2 answers

1

Missed the unsigned in the foreign key fields of tabela consulta 

-- 1 --
create database clinica;
use clinica;

-- 2 --
create table ambulatorio(
idnroa int(15) unsigned not null auto_increment,
andar int(15) not null,
capacidade smallint(45) not null,
primary key (idnroa)
);

create table medicos(
idcodm int(15) unsigned not null auto_increment,
idnroa int(15) unsigned not null ,
nome varchar(40) not null,
idade smallint(15) not null,
especialidade char(20)  not null,
cpf numeric(11) not null,
cidade varchar(30) not null,
nroa int(15) not null, 
primary key (idcodm),
foreign key (idnroa) references ambulatorio (idnroa)
);

create table pacientes(
idcodp int(15) unsigned not null auto_increment,
nome varchar(40) not null,
idade smallint(15) not null,
cidade char(30) not null,
cpf numeric(11) not null,
primary key (idcodp)
);

create table funcionarios(
idcodf int(15) unsigned not null auto_increment,
nome varchar(40) not null,
idade smallint (15) not null,
cpf numeric(11) not null,
cidade varchar(30) not null,
salario numeric(10) not null,
primary key (idcodf)
);

create table consultas(
data date not null,
hora time not null,
idcodm int(15) Unsigned not null,
idcodp int(15) Unsigned not null,
foreign key (idcodm) references medicos (idcodm),
foreign key (idcodp) references pacientes (idcodp)
);

0

You need to use Unsigned in your table consultations

create table consultas(
data date not null,
hora time not null,
idcodm int(15) Unsigned not null,
idcodp int(15) Unsigned not null,
foreign key (idcodm) references medicos (idcodm),
foreign key (idcodp) references pacientes (idcodp)
);

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.