问题描述
这是我的表，我一直收到这个错误.一切都很好，直到我执行我在底部加粗的代码.我真的快要完成这个表格了，但我已经尝试了一切来解决这个错误，但它不断出现.
Here is my table and I keep getting this error. Everything is good to go until I execute the code that I have put in bold at the bottom. Im really close to completing this table but ive tried everything to solve this error but it keeps coming up.
请帮帮我
谢谢`
更新
我采纳了你们的建议并更改了插入的数据，现在我收到错误 1136，这是列与第 1 行的值计数不匹配，这是访问 ID，因此它是一个数据类型问题，但我尝试了所有方法，但我无法弄清楚我即将完成
I took your guys suggestions and changed the inserted data now im getting an error 1136 which is column doesnt match value count for row 1 which is visit ID so its a datatype problem but ive tried everything and I cant figure it out IM SO CLOSE TO BEING DONE
请帮助！
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', '[email protected]', '2022223333', '1111 Cosmic dr'),
('P2', 'Demi Moore', '[email protected]', '2021113333', '1112 Cosmic dr'),
('P3', 'Andre Agassi', '[email protected]', '2023333333', '1113 Cosmic dr'),
('P4', 'Jet Lee', '[email protected]', '2023334444', '1114 Chinatown ct'),
('P5', 'Jim Carey', '[email protected]', '2023335555', '1115 United dr'),
('P6', 'Bruce Lee', '[email protected]', '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;
谢谢！
推荐答案
你的插入查询到 Visit 表是这样的:
Your insert query into Visit table is like this:
Insert Visit Values
('1','P1','D1','S1','2B','2014-01-03'),
值应按
(VisitID, RoomID, ServiceID, PatientID, DocID, Visit)
但它们的顺序似乎不同.
But they seem to be in a different order.
更改insert 语句以包含列名称.
Insert Visit
(VisitID, PatientID, DocID, ServiceID, RoomID, Visit)
Values
('1','P1','D1','S1','2B','2014-01-03'),
并且插入到 'RoomID' 列中的值 '2B' 似乎是 'RoomNum' 值而不是 'RoomID' 值.将其更改为 '05' 或 '06'.必须在列的所有其他输入中进行此类更改.
And the values, '2B' being inserted into 'RoomID' column seem to be 'RoomNum' values but not 'RoomID' values. Change it to '05', or '06'. Such change has to be done in all other inputs for the column.
这篇关于MySql Workbench 错误 1452 无法添加或更新子行 外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！