当mysql中有我无法理解的错误时，如何解决这段代码

8aqjt8rx  于 2021-06-19  发布在  Mysql
关注(0)|答案(2)|浏览(305)
CREATE TABLE DRIVER (
Driver_No int (4) not null unique,
Driver_Name varchar (55),
Joined_Date date,
Branch_No int (2) not null,
PRIMARY KEY (Driver_No)
);

CREATE TABLE BRANCH (
Branch_No  int (4)  not null unique,
Branch_Name VARCHAR (45),
Office_No VARCHAR (20),
Contact_Person VARCHAR (45),
PRIMARY KEY (Branch_No)
);

CREATE TABLE VEHICLE (
Vehicle_No int (6) not null unique,
Plat_Number varchar (7),
Model varchar (30),
Year year,
Last_Fleet_Maintenance date,
PRIMARY KEY (Vehicle_No)
);

CREATE TABLE FLEET (
Fleet_No int (7) not null unique,
Driver_No int (4),
Vehicle_No int (6),
Fleet_Date date,
Fleet_Type int (2),
Fleet_Status varchar (6),
PRIMARY KEY (Fleet_No)
);

CREATE TABLE FLEET_DETAILS(
Fleet_No  int (7),
Job_id VARCHAR (20),
Quantity INT (10),
Charge_amount DECIMAL (9,2),
PRIMARY KEY (Fleet_no, Job_id)
);

CREATE TABLE JOB(
Job_id varchar(10) not null unique,
Description varchar(50),
Service_Charge decimal(9,2),
Part_Charge decimal(9,2),
Unit varchar(10),
PRIMARY KEY (Job_id)
);

CREATE TABLE BILLING(
Billing_No int(10) not null unique,
Fleet_No int(8),
Payment_Type varchar(50),
Payment_Date date,
Payment_Amount decimal,
PRIMARY KEY (Billing_No)
);

ALTER TABLE driver 
ADD FOREIGN KEY (Branch_No) REFERENCES branch(Branch_No);

ALTER TABLE fleet 
ADD FOREIGN KEY (Driver_No) REFERENCES driver(Driver_No);

ALTER TABLE fleet
ADD FOREIGN KEY (Vehicle_No) REFERENCES vehicle(Vehicle_No);

ALTER TABLE fleet_details
ADD FOREIGN KEY (Fleet_No) REFERENCES fleet(Fleet_No);

ALTER TABLE fleet_details
ADD FOREIGN KEY (Job_id) REFERENCES job(Job_id);

ALTER TABLE billing
ADD FOREIGN KEY (Fleet_No) REFERENCES fleet (Fleet_No);

ALTER TABLE driver AUTO_INCREMENT=1001;

ALTER TABLE vehicle AUTO_INCREMENT=200001;

ALTER TABLE fleet AUTO_INCREMENT= 9000001;

ALTER TABLE fleet
ALTER fleet_type SET DEFAULT 01;

INSERT INTO driver (Driver_No, Driver_Name, Joined_Date, Branch_No)
VALUES  ('1001', 'Mohd Hashim Ali', '2015-09-12', '10'),
        ('1002', 'Hamzah Razali', '2015-10-14', '11'),
        ('1003', 'Arif Fadillah', '2015-10-15', '12'),
        ('1004', 'Wan Haizan Wan Omar', '2015-11-02', '10'),
        ('1005', 'Badrulhisham Mokhtar' ,'2016-01-15', '11');

INSERT INTO branch (Branch_no ,Branch_name,Office_no,Contact_person)
VALUES  ('10', 'Kuala Terengganu', '09-6671020', 'Amri Aziz'),
        ('11', 'Kemaman', '09-6951234', 'Sharifah Syed Ali'),
        ('12', 'Besut', '09-6973490', 'Rohaiza Rahmad');

INSERT INTO vehicle (Vehicle_No, Plat_Number, Model, Year, 
Last_Fleet_Maintenance)
VALUES  ('200001', 'TBS1234', 'MAN SE', '2015', '2016-01-17'),
        ('200002', 'TBS2021', 'MAN SE', '2015', '2016-01-04'),
        ('200003', 'TBU5912', 'Hino Motors', '2015', '2016-01-15'),
        ('200004', 'TBU9890', 'Isuzu Motors', '2016', '2016-01-15'),
        ('200005', 'TBV2000', 'Hino Motors', '2016', '2016-02-26'),
        ('200006', 'TBW7878', 'Hino Motors', '2016', '2015-12-02');

INSERT INTO fleet (Fleet_No, Driver_No, Vehicle_No, Fleet_Date, Fleet_Type, 
Fleet_Status)
VALUES  ('9000001', '1001', '200001', '2015-10-10', '01', 'Closed'),
        ('9000002', '1002', '200002', '2015-11-05', '01', 'Closed'),
        ('9000003', '1004', '200003', '2015-11-17', '01', 'Closed'),
        ('9000004', '1001', '200001', '2015-11-21', '02', 'WIP'),
        ('9000005', '1003', '200004', '2015-11-25', '01', 'Closed'),
        ('9000006', '1005', '200005', '2015-12-14', '01', 'Closed'),
        ('9000007', '1001', '200006', '2015-12-16', '01', 'Closed'),
        ('9000008', '1004', '200002', '2016-01-04', '01', 'Closed'),
        ('9000009', '1005', '200003', '2016-01-15', '01', 'WIP'),
        ('9000010', '1003', '200004', '2016-01-15', '02', 'WIP'),
        ('9000011', '1001', '200001', '2016-01-17', '01', 'Open'),
        ('9000012', '1002', '200005', '2016-02-26', '01', 'Open');

当我想插入车队详细信息的数据，但有一个错误，说外键约束失败错误代码：1452。无法添加或更新子行：外键约束失败( fms . fleet_details ，约束 fleet_details_ibfk_2 外键( Job_id )参考文献 job ( job_id ))

INSERT INTO fleet_details (Fleet_no,Job_id,Quantity,Charge_amount)
VALUES  ('9000001', 'Lub-01', '1', '600.00'),
        ('9000001', 'Part-01', '1', '900.00'),
        ('9000002', 'Lub-01', '1', '600.00'),
        ('9000002', 'Part-01', '1', '900.00'),
        ('9000002', 'AirC–01', '1', '1000.00'),
        ('9000003', 'Lub-02', '1', '1000.00'),
        ('9000003', 'Part-02', '2', '3400.00'),
        ('9000003', 'BA-01', '1', '400.00'),
        ('9000004', 'AirC–03', '1', '0.00'),
        ('9000005', 'Lub-03', '1', '1700.00'),
        ('9000006', 'Lub-01', '1', '600.00'),
        ('9000006', 'Part-01', '1', '900.00'),
        ('9000007', 'Lub-02', '1', '1000.00'),
        ('9000007', 'Part-02', '1', '3400.00'),
        ('9000007', 'Tyre-02', '2', '2120.00'),
        ('9000008', 'Lub-01', '1', '600.00'),
        ('9000008', 'Part-01', '1', '900.00'),
        ('9000009', 'Lub-03', '1', '1700.00'),
        ('9000009', 'Part-02', '1', '3400.00'),
        ('9000009', 'AirC – 01', '1','1000.00'),
        ('9000009', 'Tyre-01', '1', '150.00'),
        ('9000010', 'AirC–03', '1', '0.00'),
        ('9000010', 'Part-02', '1', '0.00'),
        ('9000011', 'Lub-01', '1', '600.00'),
        ('9000011', 'Part-01', '1', '900.00'),
        ('9000011', 'AirC–01', '1', '1000.00'),
        ('9000012', 'Lub-03', '1', '1700.00'),
        ('9000012', 'Part-02', '1', '3400.00'),
        ('9000012', 'BA-01', '1', '400.00');

INSERT INTO job(Job_id, Description, Service_Charge, Part_Charge, Unit)
VALUES  ('Lub-01', 'Lubricant Minor Svs', '100.00', '500.00','1 unit'),
        ('Lub-02', 'Lubricant Major Svs', '200.00', '800.00', '1 unit'),
        ('Lub-03', 'Lubricant Major Svs + Break Lubricant', '500.00', 
'1200.00', '1 unit'),
        ('Part-01', 'Spare Part Service Minor', '300.00', '600.00', '1 
unit'),
        ('Part-02', 'Spare Part Service Major', '300.00', '1400.00', '1 
unit'),
        ('Part-03', 'Spare Part Service Overhaul', '1200.00', '8000.00', '1 
unit'),
        ('AirC-01', 'Air-Cond Minor Maintanance', '400.00', '600.00', '1 
unit'),
        ('AirC-02', 'Air-Cond Major Maintanance', '700.00', '1500.00', '1 
unit'),
        ('AirC-03', 'Air-Cond Replacement', '600.00', '6000.00', '1 unit'),
        ('BA-01', 'Balancing & Alignment', '300.00', '100.00', '1 unit'),
        ('Tyre-01', 'Rotation', '1500.00', '0.00',  'N.A'),
        ('Tyre-02', 'Tyre Replacement', '60.00', '1000.00', '1 unit'),
        ('WTY-01', 'Warranty Minor', '0.00',  '0.00',  '1 unit'),
        ('WTY-02', 'Warranty Minor', '0.00',  '0.00',  '1 unit');

INSERT INTO billing(Billing_No, Fleet_No, Payment_Type, Payment_Date, 
Payment_Amount)
VALUES  ('88000001', '9000001', 'Credit',  '2015-10-10', '1500.00'),
        ('88000002', '9000002', 'Credit',  '2015-11-05', '2500.00'),
        ('88000003', '9000003', 'Credit',  '2015-11-17', '4800.00'),
        ('88000004', '9000004', 'Wty Credit', '2015-11-21', '0.00'),
        ('88000005', '9000005', 'Credit',   '2015-11-25', '1700.00'),
        ('88000006', '9000006', 'Credit',  '2015-12-14', '1500.00'),
        ('88000007', '9000007', 'Cash',   '2015-12-02', '6520.00'),
        ('88000008', '9000008', 'Credit',  '2016-01-04', '1500.00'),
        ('88000009', '9000009', 'Credit',  '2016-01-15', '6250.00'),
        ('88000010', '9000010', 'Wty Credit', '2016-01-15', '0.00'),
        ('88000011', '9000011', 'Credit',  '2016-01-17', '2500.00'),
        ('88000012', '9000012', 'Credit',  '2016-02-26', '5500.00');
mysql

2条答案

按热度按时间
gv8xihay

gv8xihay1#

你必须插入 BRANCH 插入前 DRIVERS 否则您将无法拥有匹配的外键。 JOB 也需要在 FLEET DETAILS 你的钥匙开错了
FLEET DETAILS 'AirC – 01' 而不是 'AirC–01' sql演示

赞(0）分享  回复(0）举报  2021-06-20
kqlmhetl

kqlmhetl2#

查看脚本，如果您相信insert语句尊重这些外键，则可以将所有外键声明移到代码的末尾。这是您必须移动到脚本末尾的代码：

ALTER TABLE driver 
ADD FOREIGN KEY (Branch_No) REFERENCES branch(Branch_No);

ALTER TABLE fleet 
ADD FOREIGN KEY (Driver_No) REFERENCES driver(Driver_No);

ALTER TABLE fleet
ADD FOREIGN KEY (Vehicle_No) REFERENCES vehicle(Vehicle_No);

ALTER TABLE fleet_details
ADD FOREIGN KEY (Fleet_No) REFERENCES fleet(Fleet_No);

ALTER TABLE fleet_details
ADD FOREIGN KEY (Job_id) REFERENCES job(Job_id);

ALTER TABLE billing
ADD FOREIGN KEY (Fleet_No) REFERENCES fleet (Fleet_No);

另一种忽略外键限制的方法是在每个insert语句上使用disable/enable keys语句，您可以在下一行看到一个示例：

/*!40000 ALTER TABLE `driver` DISABLE KEYS */;

INSERT INTO driver (Driver_No, Driver_Name, Joined_Date, Branch_No)
VALUES  ('1001', 'Mohd Hashim Ali', '2015-09-12', '10'),
        ('1002', 'Hamzah Razali', '2015-10-14', '11'),
        ('1003', 'Arif Fadillah', '2015-10-15', '12'),
        ('1004', 'Wan Haizan Wan Omar', '2015-11-02', '10'),
        ('1005', 'Badrulhisham Mokhtar' ,'2016-01-15', '11');

/*!40000 ALTER TABLE `driver` ENABLE KEYS */;

但是，如果要控制要插入的数据不违反外键限制，则需要按顺序进行插入，即首先在另一个表引用的表上插入数据（如果表a引用外键限制中的表b，则应首先插入表b中的数据）。

赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com