时间戳

首页 > 解决方案 > 数据库中发生冲突“

mysql - 数据库中发生冲突“

问题描述

我这里有代码，但它是我的老师写的

create database abc_quanlybanhang111111;
use abc_quanlybanhang111111;

create table abc_nhacc
(
    MaCC1 varchar(10) primary key,
    TenNhaCC varchar(50) not null,
    DiaChiCC varchar(50),
    PhoneCC varchar(11),
);

create table abc_mamh
(
    MaMH1 varchar(50) primary key,
    TenMH varchar(100),
    DonGia int,
    SoLuong int,
    MaCC1 varchar(10) foreign key references abc_nhacc(MaCC1)
);

create table abc_khachhang
(
    MaMh1 VARCHAR(10) PRIMARY KEY,
    TenKh varchar(50),
    DiaChi varchar(50),
    SĐT int,
);

CREATE TABLE abc_donhang
(
    MaDH1 varchar(10) primary key,
    NgayDH DATE default GetDate(),
    MaKH1 varchar(10) foreign key references abc_khachhang(MaMH1)
);

create table abc_chitietdonhang
(
    MaDH1 varchar(10) foreign key references abc_donhang(MaDH1) ,
    MaMH1 varchar(50) foreign key references abc_mamh(MaMH1),
    SoLuong int check (soluong>0)
        constraint pk_dmhh primary key ( MaDH1, MaMH1)
);

insert into abc_nhacc
values
    ('K001', 'THE GIOI DI DONG', '121 TRAN QUANG KHAI', '0164789720'),
    ('K002', 'NGUYEN KIM', '12 TRAN PHU', '0161792793'),
    ('K003', 'THIEN HOA', '2 BA HUYEN THANH QUAN', '094850873'),
    ('K004', 'PHONG VU', '32 LE VAN VIET QUAN 9', '85839201'),
    ('K005', 'TAN BINH', '14 NGUYEN THI DINH', '0912012901')
SELECT *
FROM abc_nhacc;

insert into abc_mamh
values
    ('S001', 'MSI ACER GAMING', 2000000, 1, 'K001');

insert into abc_mamh
values
    ('S002', 'MSI ASUS GAMING', 1000000, 10, 'K002'),
    ('S003', 'MSI GAMING', 1100000, 1, 'K003'),
    ('S004', 'LENOVO GAMING', 2000, 111, 'K004'),
    ('S005', 'IPHONE', 120000, 1, 'K005')
SELECT *
FROM abc_mamh;

insert into abc_khachhang
values
    ('KH1', 'LONG NGUYEN', '12 TRAN QUANG DIEU QUAN 1', '0938078972');

insert into abc_khachhang
values
    ('KH2', 'THONG NGUYEN', '1 TRAN DIEU QUAN 2', '0968071972'),
    ('KH3', 'THANH NGUYEN', '23 NGUYEN THI DINH QUAN 8', '0138073972'),
    ('KH4', 'THINH NGUYEN', '1 TRUONG DINH QUAN 1', '016479828'),
    ('KH5', 'LINH TRAN', '2 TRAN QUANG KHAI QUAN 1', '0938078122')
SELECT *
FROM abc_mamh;

insert into abc_chitietdonhang
values('DH001', 'MH001', 2),
    ('DH002', 'MH002', 3),
    ('DH003', 'MH003', 1),
    ('DH004', 'MH004', 9),
    ('DH004', 'MH005', 4),
    ('DH003', 'MH006', 11),
    ('DH001', 'MH007', 12);

我尝试使用删除外键的步骤修复代码，但删除后它总是显示错误：

消息 547，第 16 级，状态 0，第 72 行

INSERT 语句与 FOREIGN KEY 约束“FK__abc_chiti__MaDH1__4222D4EF”冲突。冲突发生在数据库“abc_quanlybanhang1111111”、表“dbo.abc_donhang”、列“MaDH1”中

请帮助我了解如何解决此错误。感谢。

标签： mysql

解决方案

您必须添加要在查询abc_donhang中引用的行。insert into abc_chitietdonhang ...您尝试使用 Id 引用该行DH001，但它不存在（尚不存在）。IdMH001也是如此，它也不存在于abc_mamh表中。

推荐阅读