Composite foreign key - ERROR 1215 (HY000): Cannot add Foreign key Constraint

Asked

Viewed 42 times

1

I during the creation of these 3 tables came across the error:

"ERROR 1215 (HY000): Cannot add Foreign key Constraint"

The only conclusion I could come to is that the problem lies with the field state, because when I remove it from Foreign key the error disappears.

CREATE DATABASE IF NOT EXISTS aula4exer5evolucao5;
USE aula4exer5evolucao5;

CREATE TABLE MEDICO (
    nome    varchar(20) NOT NULL,
    estado  varchar(2)  NOT NULL,
    crm     bigint      NOT NULL,
    CONSTRAINT MEDICO_PK PRIMARY KEY(estado, crm)
);

CREATE TABLE PACIENTE (
    nome            varchar(20) NOT NULL,
    dtNascimento    date        NOT NULL,
    sexo            varchar(1)  NOT NULL,
    cpf             varchar(11) NOT NULL,
    cep             bigint,
    rua             varchar(10),
    bairro          varchar(20),
    numero          INT,
    complemento     varchar(10),
    cidade          varchar(20),
    estado          varchar(2),

    CONSTRAINT PACIENTE_PK PRIMARY KEY(cpf)
);

CREATE TABLE CONSULTA_atende (
    dtHora      timestamp   NOT NULL,
    idConsulta  INT         NOT NULL AUTO_INCREMENT,
    cpf         varchar(11) NOT NULL,
    estado      varchar(2)  NOT NULL,
    crm         bigint      NOT NULL,

    CONSTRAINT CONSULTA_atende_PK PRIMARY KEY(idConsulta),
    CONSTRAINT consulta_MEDICO_FK FOREIGN KEY(estado, crm) REFERENCES MEDICO(estado, crm),
    CONSTRAINT CONSULTA_UK UNIQUE (estado, crm, dtHora)

)   ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 1;

I wanted to understand what I’m doing wrong in this third table...

mysql sql

1 answer

0

On the medical tabla, the status column is already a key Primary, so it can’t be a key Foreign, so when you take out the key Foreign the column state on the third tabla, no more error

Browser other questions tagged mysql sql

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