workbench - MySql Error Code 1136 Column Count Doesnt Match Value Count At Row 1 -
hi i'm done table ran problem can't solve.
i keep getting error 1136 column doesn't match value count row 1 visitid it's datatype problem cant figure out.
the code executed visit table code below:
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 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 * 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', 'bwillis@mail.org', '2022223333', '1111 cosmic dr'), ('p2', 'demi moore', 'moore@email.net', '2021113333', '1112 cosmic dr'), ('p3', 'andre agassi', 'agassi@mail.org', '2023333333', '1113 cosmic dr'), ('p4', 'jet lee', 'jetlee@email.net', '2023334444', '1114 chinatown ct'), ('p5', 'jim carey', 'carey@email.net', '2023335555', '1115 united dr'), ('p6', 'bruce lee', 'bruce@gmail.com', '2023336666', '1115 chinatown ct'); select* 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* 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 doctortable values ('d1','tim','edward','555-123-4567'), ('d2','andy','smith','888-777-6666'), ('d3','john','smith','222-321-7654'); select * 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 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 * visit;
there 2 problems last insertion: field docid missing values there; order between fields , values don't match.
it should like:
insert visit (visitid,patientid, docid, serviceid, roomid,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'); you find complete working code @ sqlfiddle.
Comments
Post a Comment