Bảng tblChiTietHDBH
bị lỗi foreign key. Giúp với ạ.
CREATE DATABASE QLBH17_SIEUTHI
USE QLBH17_SIEUTHI
CREATE TABLE tblNhanVien
(
iMaNV int NOT NULL,
sHoTen NVARCHAR(30) NOT NULL,
sDiaChi NVARCHAR(50) NULL,
dNgaySinh DATETIME NULL,
bGioiTinh BIT NULL
CONSTRAINT PK_tblNhanVien PRIMARY KEY (iMaNV)
);
CREATE TABLE tblNhaCC
(
iMaNCC int NOT NULL,
sTenNCC NVARCHAR(50) NULL,
sSoDT NVARCHAR(12) NULL
CONSTRAINT PK_tblNhaCC PRIMARY KEY (iMaNCC)
);
CREATE TABLE tblHangHoa
(
sMaHH NVARCHAR(10) NOT NULL,
sTenHang NVARCHAR(30) NOT NULL,
fSoLuong FLOAT NULL,
sNhanHieu NVARCHAR(30) NULL,
sDonViTinh NVARCHAR(10) NULL,
sQuyCach NVARCHAR(30) NULL,
fDonGia FLOAT NULL,
iMaNCC int NOT NULL
);
ALTER TABLE tblHangHoa
ADD CONSTRAINT PK_sMaHH PRIMARY KEY (sMaHH),
CONSTRAINT FK_HangHoa_NhaCC FOREIGN KEY (iMaNCC)
REFERENCES tblNhaCC(iMaNCC);
ALTER TABLE tblHangHoa ADD CONSTRAINT CK_HangHoa CHECK (fDonGia>0 AND fSoLuong>0 );
CREATE TABLE tblKhachHang
(
sMaKH NVARCHAR(10) NOT NULL ,
sTenKH NVARCHAR(30) NOT NULL,
dNgaySinh DATETIME NULL,
sSoDT NVARCHAR(12) NULL,
sKhuVuc NVARCHAR(50) NULL,
sEmail NVARCHAR(20) NULL,
bGioiTinh BIT
CONSTRAINT PK_tblKhachHang PRIMARY KEY (sMaKH)
);
CREATE TABLE tblHoaDonBH
(
iMaHDBH INT NOT NULL,
dNgayLap DATETIME NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
sDonViTinh NVARCHAR(10) NULL,
fThanhTien FLOAT NULL,
iMaNV INT NOT NULL,
sMaKH NVARCHAR(10) NOT NULL
);
ALTER TABLE tblHoaDonBH
ADD CONSTRAINT PK_HoaDonBH PRIMARY KEY(iMaHDBH),
CONSTRAINT FK_HoaDonBH_NhanVien FOREIGN KEY (iMaNV)
REFERENCES tblNhanVien(iMaNV),
CONSTRAINT FK_HoaDonBH_KhachHang FOREIGN KEY (sMaKH)
REFERENCES tblKhachHang(sMaKH);
ALTER TABLE tblHoaDonBH ADD CONSTRAINT CK_HoaDonBH CHECK (fDonGia > 0 and fSoLuong>0);
CREATE TABLE tblDonDatHang
(
iMaDDH int NOT NULL ,
sTenMH NVARCHAR(30) NOT NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
sDonViTinh NVARCHAR(10) NULL,
sDiaDiem NVARCHAR(50),
fThanhTien FLOAT NULL,
iMaNCC int NOT NULL,
iMaNV int NOT NULL
);
ALTER TABLE tblDonDatHang
ADD CONSTRAINT PK_DonDatHang PRIMARY KEY(iMaDDH),
CONSTRAINT FK_DonDatHang_NhaCC FOREIGN KEY (iMaNCC)
REFERENCES tblNhaCC(iMaNCC),
CONSTRAINT FK_DonDatHang_NhanVien FOREIGN KEY (iMaNV)
REFERENCES tblNhanVien(iMaNV);
ALTER TABLE tblDonDatHang ADD CONSTRAINT CK_DonDatHang CHECK (fDonGia > 0 and fSoLuong>0);
CREATE TABLE tblHoaDonNhap
(
iMaHDN int NOT NULL ,
sTenHang NVARCHAR(30) NOT NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
sDonViTinh NVARCHAR(10),
fThanhTien FLOAT NULL,
dNgayNhap DATETIME NULL,
sDiaDiem NVARCHAR(50) NULL,
sTenKho NVARCHAR(30) NULL,
iMaNV int NOT NULL,
sMaHH NVARCHAR(10) NOT NULL
);
ALTER TABLE tblHoaDonNhap
ADD CONSTRAINT PK_HoaDonNhap PRIMARY KEY(iMaHDN),
CONSTRAINT FK_HoaDonNhap_NhanVien FOREIGN KEY (iMaNV)
REFERENCES tblNhanVien(iMaNV),
CONSTRAINT FK_HoaDonNhap_HangHoa FOREIGN KEY (sMaHH)
REFERENCES tblHangHoa(sMaHH);
ALTER TABLE dbo.tblHoaDonNhap ADD CONSTRAINT CK_HoaDonNhap CHECK (fDonGia>0 AND fSoLuong>0 );
CREATE TABLE tblHoaDonXuat
(
iMaHDX int NOT NULL ,
sTenHang NVARCHAR(30) NOT NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
sDonViTinh NVARCHAR(10),
fThanhTien FLOAT NULL,
dNgayXuat DATETIME NULL,
sDiaDiem NVARCHAR(50) NULL,
sTenKho NVARCHAR(30) NULL,
iMaNV int NOT NULL,
sMaHH NVARCHAR(10) NOT NULL
);
ALTER TABLE tblHoaDonXuat
ADD CONSTRAINT PK_HoaDonXuat PRIMARY KEY(iMaHDX),
CONSTRAINT FK_HoaDonXuat_NhanVien FOREIGN KEY (iMaNV)
REFERENCES tblNhanVien(iMaNV),
CONSTRAINT FK_HoaDonXuat_HangHoa FOREIGN KEY (sMaHH)
REFERENCES tblHangHoa(sMaHH);
ALTER TABLE dbo.tblHoaDonXuat ADD CONSTRAINT CK_HoaDonXuat CHECK (fDonGia>0 AND fSoLuong>0 );
CREATE TABLE tblChiTietHDBH
(
iMaCTBH int NOT NULL,
sMaHH NVARCHAR(10) NOT NULL,
iMaHDBH int NOT NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
fThanhTien FLOAT NULL
);
ALTER TABLE tblChiTietHDBH
ADD CONSTRAINT PK_ChiTietHDBH PRIMARY KEY(iMaCTBH),
CONSTRAINT FK_ChiTietHDBH_HoaDonBH FOREIGN KEY (iMaHDBH)
REFERENCES tblHoaDonBH(iMaHDBH),
CONSTRAINT FK_ChiTietHDBH_HangHoa FOREIGN KEY (sMaHH)
REFERENCES tblHangHoa(sMaHH);
ALTER TABLE dbo.tblChiTietHDBH ADD CONSTRAINT CK_ChiTietHDBH CHECK (fDonGia>0 AND fSoLuong>0 );
CREATE TABLE tblChiTietHDNH
(
iMaCTNH int NOT NULL,
sMaHH NVARCHAR(10) NOT NULL,
iMaHDN int NOT NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
fThanhTien FLOAT NULL
);
ALTER TABLE tblChiTietHDNH
ADD CONSTRAINT PK_ChiTietHDNH PRIMARY KEY(iMaCTNH),
CONSTRAINT FK_ChiTietHDNH_HoaDonNhap FOREIGN KEY (iMaHDN)
REFERENCES tblHoaDonNhap(iMaHDN),
CONSTRAINT FK_ChiTietHDNH_HangHoa FOREIGN KEY (sMaHH)
REFERENCES tblHangHoa(sMaHH);
ALTER TABLE dbo.tblChiTietHDNH ADD CONSTRAINT CK_ChiTietHDNH CHECK (fDonGia>0 AND fSoLuong>0 );
CREATE TABLE tblChiTietHDXH
(
iMaCTXH int NOT NULL,
sMaHH NVARCHAR(10) NOT NULL,
iMaHDX int NOT NULL,
fSoLuong FLOAT NULL,
fDonGia FLOAT NULL,
fThanhTien FLOAT NULL
);
ALTER TABLE tblChiTietHDXH
ADD CONSTRAINT PK_ChiTietHDXH PRIMARY KEY(iMaCTXH),
CONSTRAINT FK_ChiTietHDXH_HoaDonXH FOREIGN KEY (iMaHDX)
REFERENCES tblHoaDonXuat(iMaHDX),
CONSTRAINT FK_ChiTietHDXH_HangHoa FOREIGN KEY (sMaHH)
REFERENCES tblHangHoa(sMaHH);
ALTER TABLE dbo.tblChiTietHDXH ADD CONSTRAINT CK_ChiTietHDXH CHECK (fDonGia>0 AND fSoLuong>0 );
------------------------------Nhập dữ liệu--------------------------
INSERT INTO tblNhanVien(iMaNV,sHoTen,sDiaChi,dNgaySinh,bGioiTinh)
VALUES
(001,N'Nguyễn Hương Giang',N'119 Giải Phóng,Hà Nội','1995-12-09',0),
(002,N'Mai Lan Hương',N'24 Kim Đồng,Hà Nội','1997-06-12',0),
(003,N'Trần Thị Hòa',N'731 Trần Hưng Đạo,Q5,TpHCM','1989-03-08',0),
(004,N'Đỗ Đức Minh',N'34 Trương Định,Q3,TpHCM','1998-10-25',1),
(005,N'Hà Duy Lập',N'34/34B Nguyễn Trãi,Q1,TpHCM','1987-08-09',1),
(006,N'Lê Nhật Minh',N'99 Ba Đình,Hà Nội','1985-06-12',1),
(007,N'Phan Thanh Duy',N'96 Định Công,Hà Nội','1996-08-30',1),
(008,N'Ngô Thanh Vân',N'873 Lê Hồng Phong,Q5,TpHCM','1986-11-30',0)
SELECT iMaNV,sHoTen,sDiaChi,dNgaySinh,CASE bGioiTinh WHEN '0' then N'Nữ' WHEN '1' THEN N'Nam' END AS bGioiTinh
FROM dbo.tblNhanVien
INSERT INTO tblNhaCC(iMaNCC,sTenNCC,sSoDT)
VALUES
(101,N'Công ty TNHH giấy Cao Phát','0952203203'),
(102,N'Công ty Gia dụng Lock&Lock','086868686'),
(103,N'Công ty cổ phần Kinh Đô','089685688'),
(104,N'Công ty Vinamilk','094545483'),
(105,N'Tập Đoàn Trung Nguyên','097454876'),
(106,N'CTY chén đĩa sứ Minh Châu','096586599'),
(107,N'Công ty hàng tiêu dùng MASAN','097546569'),
(108,N'Công ty Enchanteur','095665569'),
(109,N'Công ty Acecook Việt Nam','095625263'),
(110,N'Công ty Unilever','098862699'),
(111,N'CTy TNHH Nước Giải Khát Coca-Cola VN','098558597')
SELECT * FROM tblNhaCC
INSERT INTO tblHangHoa(sMaHH,iMaNCC,sTenHang,sNhanHieu,sQuyCach,fSoLuong,sDonViTinh,fDonGia)
VALUES
('HH011',107,N'Nước mắm Chinsu','MASAN',N' CHAI 900ml',300,'VND',45000),
('HH012',101,N'Giấy vệ sinh ','Cao Phat',N'Túi 10 cuộn',500,'VND',70000),
('HH013',108,N'Sửa tắm ','Enchanteur',N'Chai 650g',300,'VND',145000),
('HH014',110,N'Dầu gội','Dove',N'Chai 340g',400,'VND',85000),
('HH015',104,N'Sữa tươi Vinamilk','Vinamilk',N'Lốc 180ml/4 hộp',100,'VND',32000),
('HH016',105,N'Cà Phê TN G7',N'Trung Nguyên',N'Hộp 18 gói',400,'VND',40000),
('HH017',109,N'Mì ăn liền Hảo Hảo',N'Acecook',N'Cốc 67g',200,'VND',7500),
('HH018',103,N'Bánh COSY',N'Kinh Đô',N'Gói 450g',300,'VND',40000),
('HH019',111,N'Nước ngọt CocaCola',N'CoCa-CoLa',N'330ml/lốc 6 lon',402,'VND',54000),
('HH020',106,N'Bộ Chén Đĩa',N'Minh Châu ',N'Bộ 12 món',105,'VND',310000),
('HH021',102,N'Bộ cây lau nhà',N'Lock&Lock ',N'Dài 130cm',50,'VND',390000)
SELECT sMaHH,iMaNCC,sTenHang,sNhanHieu,sQuyCach,fDonGia,fSoLuong,sDonViTinh,(fDonGia*fSoluong) AS 'fThanhTien'
FROM tblHangHoa
INSERT INTO tblKhachHang(sMaKH,sTenKH,sKhuVuc,dNgaySinh,sEmail,sSoDT,bGioiTinh)
VALUES
('KH001',N'Nguyễn Văn Tài',N'731 Trần Hưng Đạo,Q5,tpHCM','1987-08-09','[email protected]','098559669',1),
('KH002',N'Trần Đình Trọng',N'32/3 Trần Bình Trọng,Q5,TpHCM','1987-08-09','[email protected]','095745649',1),
('KH003',N'Trần Thị Thanh',N'40/12 Phan Đình Giót,Hà Nội','1998-08-25','[email protected]','095258945',0),
('KH004',N'Mai Lan Hương',N'30 Kim Lũ,Hà Nội','1988-07-20','[email protected]','098552552',0),
('KH005',N'Trần Kim Hoa',N'38 Phùng Hưng,Hà Nội','1996-10-25','[email protected]','098526577',0),
('KH006',N'Ngô Văn Lâm',N'52 Trần Khát Trân,Bắc Giang','1978-07-05','[email protected]','098256458',1),
('KH007',N'Đào Thị Nguyệt',N'185 Trạch Ray,tp Hải Phòng','1898-01-25','[email protected]','098558329',0),
('KH008',N'Đỗ Thị Nhung',N'99 Nguyễn Tất Tố,tp Hải Phòng','1878-09-15','[email protected]','089285997',0)
SELECT sMaKH,sTenKH,sKhuVuc,dNgaySinh,sEmail,sSoDT,CASE bGioiTinh WHEN '0' then N'Nữ' WHEN '1' THEN N'Nam' END AS bGioiTinh
FROM dbo.tblKhachHang
INSERT INTO tblHoaDonBH(iMaHDBH,sMaKH,iMaNV,dNgayLap,fDonGia,fSoLuong,sDonViTinh)
VALUES
(1001,'KH008',001,'2019-01-12',70000,5,'VND'),
(1002,'KH008',002,'2019-01-20',145000,3,'VND'),
(1003,'KH001',005,'2019-05-20',310000,5,'VND'),
(1004,'KH005',007,'2019-07-19',390000,2,'VND'),
(1005,'KH007',004,'2018-10-25',32000,10,'VND'),
(1006,'KH002',008,'2019-06-10',85000,3,'VND'),
(1007,'KH003',003,'2018-12-09',40000,5,'VND'),
(1008,'KH006',007,'2019-08-12',54000,10,'VND'),
(1009,'KH004',007,'2018-09-29',390000,2,'VND')
SELECT iMaHDBH,sMaKH,iMaNV,dNgayLap,fDonGia,fSoLuong,sDonViTinh,(fDonGia*fSoluong) AS 'fThanhTien'
FROM dbo.tblHoaDonBH
INSERT INTO tblDonDatHang(iMaDDH,iMaNCC,iMaNV,sTenMH,sDiaDiem,fDonGia,fSoLuong,sDonViTinh)
VALUES
(2001,106,008,N'Bộ Chén Đĩa',N'28 Lý Thường Kiệt,Tp Hải Phòng',310000,5,'VND'),
(2002,101,008,N'Giấy vệ sinh',N'23/19 Nam Trung Hành,Tp Hải Phòng',700000,7,'VND'),
(2003,102,001,N'Bộ cây lau nhà',N'Bạch Đằng2,Tp Hải Phòng',390000,4,'VND'),
(2004,103,005,N'Bánh COSY',N'46 Phương Mai,Tp Hà Nội',40000,10,'VND'),
(2005,102,007,N'Bộ cây lau nhà',N'185 Hoàng Văn Thái,Tp Hà Nội',390000,2,'VND'),
(2006,105,006,N'Cà Phê TN G7',N'129 Giảng Võ,Tp Hà Nội',40000,15,'VND'),
(2007,106,004,N'Bộ Chén Đĩa',N'99 Nguyễn Thị Minh Khai,Tp HCM',310000,2,'VND'),
(2008,104,002,N'Lốc Sửa tươi Vinamilk',N'Tôn Đức Thắng,Tp HCM',32000,25,'VND')
SELECT iMaDDH,iMaNCC,iMaNV,sTenMH,sDiaDiem,fDonGia,fSoLuong,sDonViTinh,(fDonGia*fSoluong) AS 'fThanhTien'
FROM tblDonDatHang
INSERT INTO tblHoaDonNhap(iMaHDN,iMaNV,sMaHH,sTenHang,sDiaDiem,sTenKho,dNgayNhap,fDonGia,fSoLuong,sDonViTinh)
VALUES
(3001,001,'HH011',N'Nước mắm Chinsu',N'Hà Nội','KhoA01','2018-01-01',45000,300,'VND'),
(3002,008,'HH012',N'Giấy vệ sinh',N'Hà Nội','KhoA02','2018-01-03',70000,500,'VND'),
(3003,007,'HH014',N'Dầu gội Dove',N'Hà Nội','KhoA02','2018-01-02',85000,400,'VND'),
(3004,008,'HH016',N'Cà Phê TN G7',N'Hà Nội','KhoA03','2018-01-03',40000,400,'VND'),
(3005,008,'HH021',N'Bộ cây lau nhà',N'Hà Nội','KhoA04','2018-01-03',390000,50,'VND'),
(3006,001,'HH020',N'Bộ Chén Đĩa',N'Hà Nội','KhoA04','2018-01-02',310000,105,'VND'),
(3007,001,'HH019',N'Nước ngọt CocaCola',N'Hà Nội','KhoA03','2018-01-01',54000,402,'VND'),
(3008,008,'HH013',N'Sửa tắm Enchanteur',N'Hà Nội','KhoA02','2018-01-03',145000,300,'VND'),
(3009,008,'HH018',N'Bánh COSY',N'Hà Nội','KhoA03','2018-01-03',40000,300,'VND'),
(3010,007,'HH017',N'Mì ăn liền Hảo Hảo',N'Hà Nội','KhoA03','2018-01-02',7500,200,'VND'),
(3011,007,'HH015',N'Sữa tươi Vinamilk',N'Hà Nội','KhoA03','2018-01-02',32000,100,'VND')
SELECT iMaHDN,iMaNV,sMaHH,sTenHang,sTenKho,sDiaDiem,dNgayNhap,fDonGia,fSoLuong,sDonViTinh,(fDonGia*fSoluong) AS 'fThanhTien'
FROM tblHoaDonNhap
INSERT INTO tblHoaDonXuat(iMaHDX,iMaNV,sMaHH,sTenHang,sDiaDiem,sTenKho,dNgayXuat,fDonGia,fSoLuong,sDonViTinh)
VALUES
(5001,001,'HH011',N'Nước mắm Chinsu',N'Hà Nội','KhoA01','2018-01-11',45000,300,'VND'),
(5002,008,'HH012',N'Giấy vệ sinh',N'Hà Nội','KhoA02','2018-01-13',70000,500,'VND'),
(5003,007,'HH014',N'Dầu gội Dove',N'Hà Nội','KhoA02','2018-01-12',85000,400,'VND'),
(5004,008,'HH016',N'Cà Phê TN G7',N'Hà Nội','KhoA03','2018-01-13',40000,400,'VND'),
(5005,008,'HH021',N'Bộ cây lau nhà',N'Hà Nội','KhoA04','2018-01-13',390000,50,'VND'),
(5006,001,'HH020',N'Bộ Chén Đĩa',N'Hà Nội','KhoA04','2018-01-12',310000,105,'VND'),
(5007,001,'HH019',N'Nước ngọt CocaCola',N'Hà Nội','KhoA03','2018-01-11',54000,402,'VND'),
(5008,008,'HH013',N'Sửa tắm Enchanteur',N'Hà Nội','KhoA02','2018-01-13',145000,300,'VND'),
(5009,008,'HH018',N'Bánh COSY',N'Hà Nội','KhoA03','2018-01-03',40000,300,'VND'),
(5010,007,'HH017',N'Mì ăn liền Hảo Hảo',N'Hà Nội','KhoA03','2018-01-12',7500,200,'VND'),
(5011,007,'HH015',N'Sữa tươi Vinamilk',N'Hà Nội','KhoA03','2018-01-12',32000,100,'VND')
SELECT iMaHDX,iMaNV,sMaHH,sTenHang,sTenKho,sDiaDiem,dNgayXuat,fDonGia,fSoLuong,sDonViTinh,(fDonGia*fSoluong) AS 'fThanhTien'
FROM tblHoaDonXuat
INSERT INTO tblChiTietHDBH(iMaCTBH,iMaHDBH,sMaHH,fDonGia,fSoLuong)
VALUES
(701,1001,'HH012',70000,5),
(702,1009,'HH021',390000,2),
(703,1008,'HH018',54000,10),
(704,1002,'HH013',145000,3),
(705,1003,'HH020',310000,5),
(706,1007,'HH016',40000,5),
(707,1006,'HH014',85000,3),
(708,1004,'HH021',390000,2),
(709,1005,'HH015',32000,10)
SELECT iMaCTBH,iMaHDBH,sMaHH,fDonGia,fSoLuong,(fDonGia*fSoluong) AS 'fThanhTien'
FROM tblChiTietHDBH