MySql Workbench错误1452无法添加或更新子行-外键约束失败

Question

这是我的表，我一直收到这个错误。在我执行底部粗体的代码之前，一切都很好。我真的快要完成这张表了，但我尽了一切努力来解决这个错误，但它一直在出现。

请帮我

谢谢`

更新

我接受了你们的建议，现在更改了插入的数据。我得到了一个错误1136，这是一列与第1行的值计数不匹配，第1行是访问ID，所以这是一个数据类型问题，但我尝试了所有方法，但我无法解决

请帮忙！

Create Schema Visit;

create table roomtableS(
RoomID char (2)     not null,
RoomNum char (2)    not null,
Charge integer not null,
CONSTRAINT RoomTable_PK Primary Key(RoomID));

Insert into roomtableS values
('01','1A',125.00),
('02','1A',150.00),
('03','1A',100.00),
('04','1A',200.00),
('05','2B',150.00),
('06','2B',125.00),
('07','3C',200.00),
('08','3C',125.00),
('09','3C',100.00);


SELECT * FROM ROOMTABLES;

create table PATIENT(
PatientID    char(5)    not null, 
PatientName  Char(25) not null, 
PatientEmail     Char(30) null,
PatientPhoneNumber Char(10) null,
PatientAddress    Char(100) null,
constraint PATIENT_PK Primary key(PatientID));
insert PATIENT values
('P1', 'Bruce Willis', '[e-mail]', '2022223333', '1111 Cosmic dr'),
('P2', 'Demi Moore', '[e-mail]', '2021113333', '1112 Cosmic dr'),
('P3', 'Andre Agassi', '[e-mail]', '2023333333', '1113 Cosmic dr'),
('P4', 'Jet Lee', '[e-mail]', '2023334444', '1114 Chinatown ct'),
('P5', 'Jim Carey', '[e-mail]', '2023335555', '1115 United dr'),
('P6', 'Bruce Lee', '[e-mail]', '2023336666', '1115 Chinatown ct');
select* From PATIENT;


Create table SERVICETable(
ServiceID       Char (5) not null,
ServiceTreatment Char(25) not null,
ServiceCost     numeric  not null,
constraint  SERVICE_PK Primary Key(ServiceID));
insert SERVICETable values
('S1','Sore throat', 10.00),
('S2', 'Fever', 15.00),
('S3', 'Headache', 10.00),
('S4', 'Blood pressusre', 20.00),
('S5', 'Yearly checkup', 30.00),
('S6', 'Common cold', 15.00);
select* from SERVICETable;

Create Table doctortable(
DocID char (5)   NOT NULL,
DoctorFirstName char(15) Not NULL,
DoctorLastName char (15) Not Null,
DoctorPhone char (15) Not Null,
CONSTRAINT DoctorTable_PK Primary Key(DocID));

INSERT INTO doctortable values
('D1','Tim','Edward','555-123-4567'),
('D2','Andy','Smith','888-777-6666'),
('D3','John','Smith','222-321-7654');



Select * From doctortable;

Create Table visit(
VisitID char (2) not Null,
PatientID Char (5) not null,
DocID Char (5) not null,
ServiceID Char (5)  not Null,
RoomID char (2) not Null,
Visit date  not null,
CONSTRAINT   VISIT_PK    PRIMARY KEY (VisitID));

Alter table Visit 
add foreign key (PatientID)
references Patient (PatientID);

Alter table Visit 
add foreign key (DocID)
references doctortable (DocID);
Alter table Visit 
add foreign key (ServiceID)
references ServiceTable (ServiceID);
Alter table Visit 
add foreign key (RoomID)
references roomtableS (RoomID);

Insert into Visit (VisitID,RoomID,ServiceID,PatientID,Visit) values
**('1','P1','D1','S1','05','2014-01-03'),
('2','P2','D2','S2','01','2014-01-10'),
('3','P3','D1','S3','02','2014-01-10'),
('4','P4','D2','S4','07','2014-01-15'),
('5','P1','D3','S2','08','2014-01-10'),
('6','P5','D3','S5','03','2014-02-02'),
('7','P4','D1','S6','06','2014-01-10'),
('8','P3','D2','S5','03','2014-02-03'),
('9','P2','D3','S6','01','2014-02-04'),
('10','P3','D1','S2','06','2014-02-04'),
('11','P5','D2','S4','04','2014-02-05'),
('12','P4','D1','S5','09','2014-02-06');**
Select * from Visit;

谢谢！

Answer 1

在Visit表中插入查询如下：

Insert Visit Values
('1','P1','D1','S1','2B','2014-01-03'),

预计数值按的顺序排列

(VisitID, RoomID, ServiceID, PatientID, DocID, Visit) 

但它们的顺序似乎不同。

更改insert语句以包含列名。

Insert Visit 
(VisitID, PatientID, DocID, ServiceID, RoomID, Visit) 
Values
('1','P1','D1','S1','2B','2014-01-03'),

并且插入到'RoomID'列中的值'2B'似乎是'RoomNum'值而不是'RoomID'值。将其更改为'05'或'06'。这种更改必须在该列的所有其他输入中进行。