mysql - 当我尝试在 MySQL 中创建表时，它给出错误代码 1215 : “Cannot add foreign key constraint”

Question

我正在尝试创建数据库，但是当我运行此代码时，出现此错误:

Code 1215: “Cannot add foreign key constraint"

代码:

drop database if exists kat_db;

create database if not exists kat_db;

use kat_db;

create table if not exists Patients
(
    PatientID int not null PRIMARY KEY,
    PatientName varchar(50),
    TypeOfAnimal varchar(50),
    DateOfLastApt date,
    PatientAge int,
    Microchipped boolean,
    OwnerID int not null,
    DoctorID int not null,

    foreign key (OwnerID) references Owners(OwnerID),
    foreign key (DoctorID) references Doctors(DoctorID)
);

create table if not exists Owners
(
    OwnerID int not null PRIMARY KEY,
    OwnerFirstName varchar(50),
    OwnerLastName varchar(50),
    PatientID int,
    OwnerPhone int,
    OwnerZip int,
    PreferredPaymentMethod varchar(50),

    foreign key (PatientID) references Patients(PatientID)
);

create table if not exists Doctors
(
    DoctorID int not null PRIMARY KEY,
    DoctorFirstName varchar(50),
    DoctorLastName varchar(50),
    PatientID int,
    DoctorPhone int,
    DoctorZip int,
    AnimalSpecialty varchar(50),

    foreign key (PatientID) references Patients(PatientID)
);

insert into Patients (PatientID, TypeOfAnimal, PatientAge, PatientName, DateOfLastApt, Microchipped, DoctorID, OwnerID)
values (01, 'dog', 6, 'Dima', '2017-02-05', 1, 101, 1001),
       (02, 'dog', 3, 'Misha', '2017-02-05', 1, 102, 1002),
       (03, 'cat', 2, 'Pistol', '2017-06-09', 0, 103, 1003),
       (04, 'cat', 1, 'Nessie', '2017-09-04', 0, 104, 1004),
       (05, 'cat', 2, 'Charlie', '2016-06-04', 1, 105, 1005),
       (06, 'cat', 12, 'Francis', '2015-05-07', 1, 106, 1006),
       (07, 'rabbit', 5, 'Bunny', '2015-05-06', 0, 107, 1007),
       (08, 'turtle', 6, 'Aqua', '2017-06-08', 0, 108, 1008),
       (09, 'dog', 16, 'Sammie', '2012-09-07', 1, 109, 1009),
       (10, 'dog', 14, 'Dog', '2016-04-06', 0, 110, 1010);

insert into Owners (OwnerID, OwnerFirstName, OwnerLastName, OwnerPhone, OwnerZip, PreferredPaymentMethod, PatientID)
values (101, 'Kat', 6, 'Dima', '2017-02-05', 1, 101, 1001),
       (102, 'Hunter', 3, 'Misha', '2017-02-05', 1, 102, 1002),
       (103, 'Vicky', 2, 'Pistol', '2017-06-09', 0, 103, 1003),
       (104, 'Vanessa', 1, 'Nessie', '2017-09-04', 0, 104, 1004),
       (105, 'Weston', 2, 'Charlie', '2016-06-04', 1, 105, 1005),
       (106, 'Jonas', 12, 'Francis', '2015-05-07', 1, 106, 1006),
       (107, 'Devin', 5, 'Bunny', '2015-05-06', 0, 107, 1007),
       (108, 'Grego', 6, 'Aqua', '2017-06-08', 0, 108, 1008),
       (109, 'Jackson', 16, 'Sammie', '2012-09-07', 1, 109, 1009),
       (110, 'dog', 14, 'Dog', '2016-04-06', 0, 110, 1010);

insert into Patients (PatientID, TypeOfAnimal, PatientAge, PatientName, DateOfLastApt, Microchipped, DoctorID, OwnerID)
values (01, 'dog', 6, 'Dima', '2017-02-05', 1, 101, 1001),
       (02, 'dog', 3, 'Misha', '2017-02-05', 1, 102, 1002),
       (03, 'cat', 2, 'Pistol', '2017-06-09', 0, 103, 1003),
       (04, 'cat', 1, 'Nessie', '2017-09-04', 0, 104, 1004),
       (05, 'cat', 2, 'Charlie', '2016-06-04', 1, 105, 1005),
       (06, 'cat', 12, 'Francis', '2015-05-07', 1, 106, 1006),
       (07, 'rabbit', 5, 'Bunny', '2015-05-06', 0, 107, 1007),
       (08, 'turtle', 6, 'Aqua', '2017-06-08', 0, 108, 1008),
       (09, 'dog', 16, 'Sammie', '2012-09-07', 1, 109, 1009),
       (10, 'dog', 14, 'Dog', '2016-04-06', 0, 110, 1010);

我尝试以不同的顺序创建表。我还创建了一个不同的数据库，它起作用了，事实上，我用它在顶部创建了数据库。

drop database if exists kat3_db;
create database if not exists kat3_db;
use kat3_db;
create table if not exists Book(
BookNumber int not null,
BookName varchar(50),
BookPrice decimal(5,2),
CoverType varchar(15) default 'hardcover',
PublicationDate date,
primary key (BookNumber)
);

create table if not exists Course(
CourseNo int not null,
CourseName varchar(20),
Semester varchar(10),
BookNumber int not null,
primary key (CourseNo),
foreign key (BookNumber) references Book(BookNumber)
);

insert into Book
(BookNumber, BookName, BookPrice, CoverType, PublicationDate)
values 
(1, 'Math', 023.64, 'paperback', '1999-02-05'),
(3, 'English', 999.36, 'e-book', '2013-06-04'),
(5, 'Calc', 056.69, 'paperback', '1964-05-05'),
(6, 'C++', 053.98, 'paperback', '2016-03-04'),
(7, 'Programming', 113.02, 'paperback', '2001-10-11'),
(9, 'Art', 250.99, 'paperback', '1996-11-11'),
(10, 'Networking', 036.64, 'e-book', '2014-05-06'),
(12, 'Drawing', 111.36, 'paperback', '2013-06-04'),
(13, 'Robotics', 012.03, 'e-book', '2016-06-05'),
(14, 'Computer', 25.03, 'e-book', '2001-06-04')
;

insert into Book
(BookNumber, BookName, BookPrice, PublicationDate)
values
(2, 'Reading', 364.20, '2016-06-05'),
(4, 'Database', 036.64, '2017-06-05'),
(8, 'Wellness', 050.00, '1999-10-10'),
(11, 'Linux', 55.36, '2013-06-08'),
(15, 'FYE', 03.01, '1991-02-05')
;

insert into Course
(CourseNo, CourseName, Semester, BookNumber)
values
(111, 'IntroToMath', 'Spring', 1),
(222, 'IntroToReading', 'Fall', 2),
(333, 'IntroToEnglish', 'Spring', 3),
(444, 'IntroToDatabase', 'Fall', 4),
(555, 'IntroToCalc', 'Spring', 5),
(666, 'IntroToC++', 'Spring', 6),
(777, 'IntroToProgramming', 'Fall', 7),
(888, 'IntroToWellness','Spring', 8),
(999, 'IntroToArt', 'Fall', 9),
(010, 'IntroToNetworking', 'Spring', 10),
(011, 'IntroToLinux', 'Fall', 11),
(012, 'IntroToDrawing', 'Spring', 12),
(013, 'IntroToRobotics', 'Fall', 13),
(014, 'IntroToComputer', 'Spring', 14),
(015, 'IntroToFYE', 'Summer', 15)
;

我看不出我做了什么不同的事情。任何建议将不胜感激。如果有人可以在上面运行第一个代码并告诉我它是否适合他们，那也会有很大的帮助。谢谢!

Answer 1

在创建患者表时，所有者和医生表信息不可用。所以它不允许你。

所以只需先创建表即可。然后用alter table添加FK。