Lỗi xung đột dữ liệu trong SQL

Chaylok_C_u · May 10, 2022, 2:10pm

Đề bài yêu cầu:

Đơn vị tính của sản phẩm chỉ có thể là (“cay”,”hop”,”cai”,”quyen”,”chuc”).

Khi khao báo:

ALTER TABLE SANPHAM
ADD CONSTRAINT SANPHAM_DVT CHECK (DVT = 'cay' OR DVT = 'hop' OR DVT = 'cai' OR DVT = 'quyen' OR DVT = 'chuc').

Thì nó báo lỗi như trong hình, mọi người chỉ em sửa lỗi với.

nguyenhuuca · October 11, 2015, 3:57am

Table san pham có dữ liệu chưa? tên constraint có bị trùng không?

Chaylok_C_u · October 11, 2015, 3:58am

Đã tạo và có dữ liệu hết rồi .

nguyenhuuca · October 11, 2015, 3:59am

Cái cột dvt có cái nào nằm ngoài cái check trên thì đổi lại xem.

Chaylok_C_u · October 11, 2015, 4:00am

cũng không trùng . trước đó có constraint K_cuu .

Chaylok_C_u · October 11, 2015, 10:06am

không hiểu cho lắm , mới nhập môn dc 2 tuần đây là bảng SanPham

CREATE TABLE SANPHAM
(
	MASP char(4)primary key,
	TENSP varchar(40),
	DVT varchar(20),
	NUOCSX varchar(40),
	GIA money
)

nguyenhuuca · October 11, 2015, 4:03am

cho xem dữ liệu đã tạo?

Chaylok_C_u · October 11, 2015, 10:06am

CREATE DATABASE QLBH
CREATE TABLE KHACHHANG
(
	MAKH char(4) primary key,
	HOATEN varchar(40),
	DCHI varchar(50),
	SODT varchar(20),
	NGSINH smalldatetime,
	DOANHSO money,
	NGDK smalldatetime
	
)
CREATE TABLE NHANVIEN
(
	MANV char(4)primary key,
	HOTEN varchar(40),
	SODT varchar(20),
	NGVL smalldatetime
)
CREATE TABLE SANPHAM
(
	MASP char(4)primary key,
	TENSP varchar(40),
	DVT varchar(20),
	NUOCSX varchar(40),
	GIA money
)
CREATE TABLE HOADON
(
	SOHD int primary key,
	NGHD smalldatetime,
	MAKH char(4),
	MANV char(4),
	TRIGIA money
)
CREATE TABLE CTHD
(
	SOHD int foreign key (SOHD) 
	references HOADON(SOHD),

	MASP char(4)foreign key (MASP) 
	references SANPHAM(MASP),
	SL int,
	constraint K_CUU 
	primary key (SOHD, MASP)
	
)
insert into KHACHHANG
VALUES ('KH01','Nguyen Van A','731 Tran Hung Dao, Q5, TpHCM','08823451','10/20/1960','13,060,000','07/22/2006')
insert into KHACHHANG
VALUES ('KH02','Tran Ngoc Han','23/5 Nguyen Trai, Q5, TpHCM','0908256478','4/3/1974','280,000','07/30/2006')
INSERT INTO KHACHHANG
VALUES('KH03','Tran Ngoc Linh','45 Nguyen Canh Chan, Q1, TpHCM','0938776266','6/12/1980','3,860,000','08/05/2006')
INSERT INTO KHACHHANG
VALUES('KH04','Tran Minh Long','50/34 Le Dai Hanh, Q10, TpHCM','0917325476','3/9/1965','250,000','10/02/2006')
INSERT INTO KHACHHANG
VALUES('KH05','Le Nhat Minh','34 Truong Dinh, Q3, TpHCM','08246108','3/10/1950','21,000','10/28/2006')
INSERT INTO KHACHHANG
VALUES('KH06','Le Hoai Thuong','227 Nguyen Van Cu, Q5, TpHCM','08631738','12/31/1981','915,000','11/24/2006')
INSERT INTO KHACHHANG
VALUES ('KH07','NGUYEN VAN TAM','32/3 TRAN BINH TRONG,Q5,TP.HCM','0916783565','4/6/1971','12,500','12/01/2006')
INSERT INTO KHACHHANG
VALUES ('KH08','PHAN THI THANH','45/2 AN DUONG VUONG,Q5,TP.HCM','0938435756','1/10/1971','365,000','12/13/2006')
INSERT INTO KHACHHANG
VALUES ('KH09','LE HA VINH','873 LE HONG PHONG,Q5,TP.HCM','08654763','9/3/1979','70,000','01/14/2007')
INSERT INTO KHACHHANG
VALUES ('KH10','HA DUY LAP','34/34B NGUYEN TRAI,Q1,TP.HCM','08768904','5/2/1983','67,500','01/16/2007')
insert into NHANVIEN
VALUES ('NV01','Nguyen Nhu Nhut','0927345678','4/13/2006')
insert into NHANVIEN
VALUES ('NV02','Le Thi Phi Yen','0987567390','4/21/2006')
INSERT INTO NHANVIEN
VALUES ('NV03','Nguyen Van B','0997047382','4/27/2006')
INSERT INTO NHANVIEN
VALUES ('NV04','Ngo Thanh Tuan','0913758498','6/24/2006')
INSERT INTO NHANVIEN
VALUES ('NV05','Nguyen Thi Truc Thanh','0918590387','7/20/2006')
INSERT INTO SANPHAM
VALUES ('BC01','But chi','cay','Singapore','3,000')
INSERT INTO SANPHAM
VALUES ('BC02','But chi','cay','Singapore','5,000')
INSERT INTO SANPHAM
VALUES ('BC03','But chi','cay','Viet Nam','3,500')
INSERT INTO SANPHAM
VALUES ('BC04','But chi','hop','Viet Nam','30,000')
INSERT INTO SANPHAM
VALUES ('BB01','But bi','cay','Viet Nam','5,000')
INSERT INTO SANPHAM
VALUES ('BB02','But bi','cay','Trung Quoc','7,000')
INSERT INTO SANPHAM
VALUES ('BB03','But bi','hop','Thai Lan','100,000')
INSERT INTO SANPHAM
VALUES ('TV01','Tap 100 giay mong','quyen','Trung Quoc','2,500')
INSERT INTO SANPHAM
VALUES ('TV02','Tap 200 giay mong','quyen','Trung Quoc','4,500')
INSERT INTO SANPHAM
VALUES ('TV03','Tap 100 giay tot','quyen','Viet Nam','3,000')
INSERT INTO SANPHAM
VALUES ('TV04','Tap 200 giay tot','quyen','Viet Nam','5,500')
INSERT INTO SANPHAM
VALUES ('TV05','Tap 100 trang','chuc','Viet Nam','23,000')
INSERT INTO SANPHAM
VALUES ('TV06','Tap 200 trang','chuc','Viet Nam','53,000')
INSERT INTO SANPHAM
VALUES ('TV07','Tap 100 trang','chuc','Trung Quoc','34,000')
INSERT INTO SANPHAM
VALUES ('ST01','So tay 500 trang','quyen','Trung Quoc','40,000')
INSERT INTO SANPHAM
VALUES ('ST02','So tay loai 1','quyen','Viet Nam','55,000')
INSERT INTO SANPHAM
VALUES ('ST03','So tay loai 2','quyen','Viet Nam','51,000')
INSERT INTO SANPHAM
VALUES ('ST04','So tay','quyen',' Thai Lan','55,000')
INSERT INTO SANPHAM
VALUES ('ST05','So tay mong','quyen',' Thai Lan','20,000')
INSERT INTO SANPHAM
VALUES ('ST06','Phan viet bang','hop','Viet Nam','5,000')
INSERT INTO SANPHAM
VALUES ('ST07','Phan khong bui','hop','Viet Nam','7,000')
INSERT INTO SANPHAM
VALUES ('ST08',' Bong bang','Viet Nam','cai','1,000')
INSERT INTO SANPHAM
VALUES ('ST09','But long','cay','Viet Nam','5,000')
INSERT INTO SANPHAM
VALUES ('ST10','But long','cay','Trung Quoc','7,000')
INSERT INTO HOADON
VALUES ('1001','07/23/2006 ','KH01','NV01','320,000')
INSERT INTO HOADON
VALUES ('1002','08/12/2006','KH01','NV02','840,000')
INSERT INTO HOADON
VALUES ('1003','08/23/2006','KH02','NV01','100,000')
INSERT INTO HOADON
VALUES ('1004','09/01/2006','KH02','NV01','180,000')
INSERT INTO HOADON
VALUES ('1005','10/20/2006','KH01','NV02','3,800,000')
INSERT INTO HOADON
VALUES ('1006','10/16/2006','KH01','NV03','2,430,000')
INSERT INTO HOADON
VALUES ('1007','10/28/2006','KH03','NV03','510,000')
INSERT INTO HOADON
VALUES ('1008','10/28/2006','KH01','NV03','440,000')
INSERT INTO HOADON
VALUES ('1009','10/28/2006','KH03','NV04','200,000')
INSERT INTO HOADON
VALUES ('1010','11/01/2006','KH01','NV01',' 5,200,000')
INSERT INTO HOADON
VALUES ('1011','11/04/2006','KH04','NV03',' 250,000')
INSERT INTO HOADON
VALUES ('1012','11/30/2006','KH05','NV03','21,000')
INSERT INTO HOADON
VALUES ('1013','12/12/2006','KH06','NV01','5,000')
INSERT INTO HOADON
VALUES ('1014','12/31/2006','KH03','NV02','3,150,000')
INSERT INTO HOADON
VALUES ('1015','01/01/2007','KH06','NV01','910,000')
INSERT INTO HOADON
VALUES ('1016','01/01/2007','KH07','NV02','12,500')
INSERT INTO HOADON
VALUES ('1017','01/02/2007','KH08','NV03','35,000')
INSERT INTO HOADON
VALUES ('1018','01/13/2007','KH08','NV03','330,000')
INSERT INTO HOADON
VALUES ('1019','01/13/2007','KH01','NV03','30,000')
INSERT INTO HOADON
VALUES ('1020','01/14/2007','KH09','NV04','70,000')
INSERT INTO HOADON
VALUES ('1021','01/16/2007','KH10','NV03','67,500')
INSERT INTO HOADON
VALUES ('1022','01/16/2007','Null','NV03','7,000')
INSERT INTO HOADON
VALUES ('1023','01/17/2007','Null','NV01','330,000')
INSERT INTO CTHD
VALUES ('1001','TV02','10')
INSERT INTO CTHD
VALUES ('1001','ST01','5')
INSERT INTO CTHD
VALUES ('1001','BC01','5')
INSERT INTO CTHD
VALUES ('1001','BC02','10')
INSERT INTO CTHD
VALUES ('1001','ST08','20')
INSERT INTO CTHD
VALUES ('1002','BC04','20')
INSERT INTO CTHD
VALUES ('1002','BB01','20')
INSERT INTO CTHD
VALUES ('1002','BB02','20')
INSERT INTO CTHD
VALUES ('1003','BB03','10')
INSERT INTO CTHD
VALUES ('1004','TV01','20')
INSERT INTO CTHD
VALUES ('1004','TV02','10')
INSERT INTO CTHD
VALUES ('1004','TV03','10')
INSERT INTO CTHD
VALUES ('1005','TV04','10')
INSERT INTO CTHD
VALUES ('1005','TV05','50')
INSERT INTO CTHD
VALUES ('1004','TV06','50')
INSERT INTO CTHD
VALUES ('1006','TV07','20')
INSERT INTO CTHD
VALUES ('1006','ST01','300')
INSERT INTO CTHD
VALUES ('1006','ST02','10')
INSERT INTO CTHD
VALUES ('1007','ST03','10')
INSERT INTO CTHD
VALUES ('1008','ST04','8')
INSERT INTO CTHD
VALUES ('1009','ST05','10')
INSERT INTO CTHD
VALUES ('1010','TV07','100')
INSERT INTO CTHD
VALUES ('1010','ST07','50')
INSERT INTO CTHD
VALUES ('1010','ST08','50')
INSERT INTO CTHD
VALUES ('1010','ST04','50')
INSERT INTO CTHD
VALUES ('1010','TV03','100')
INSERT INTO CTHD
VALUES ('1011','ST06','50')
INSERT INTO CTHD
VALUES ('1012','ST07','3')
INSERT INTO CTHD
VALUES ('1013','ST08','5')
INSERT INTO CTHD
VALUES ('1014','BC02','80')
INSERT INTO CTHD
VALUES ('1014','BB02','100')
INSERT INTO CTHD
VALUES ('1014','BC04','60')
INSERT INTO CTHD
VALUES ('1014','BB01','50')
INSERT INTO CTHD
VALUES ('1015','BB02','30')
INSERT INTO CTHD
VALUES ('1015','BB03','7')
INSERT INTO CTHD
VALUES ('1016','TV01','5')
INSERT INTO CTHD
VALUES ('1017','TV02','1')
INSERT INTO CTHD
VALUES ('1017','TV03','1')
INSERT INTO CTHD
VALUES ('1017','TV04','5')
INSERT INTO CTHD
VALUES ('1018','ST04','6')
INSERT INTO CTHD
VALUES ('1019','ST05','1')
INSERT INTO CTHD
VALUES ('1019','ST06','2')
INSERT INTO CTHD
VALUES ('1020','ST07','10')
INSERT INTO CTHD
VALUES ('1021','ST08','5')
INSERT INTO CTHD
VALUES ('1021','TV01','7')
INSERT INTO CTHD
VALUES ('1021','TV02','10')
INSERT INTO CTHD
VALUES ('1022','ST07','1')
INSERT INTO CTHD
VALUES ('1023','ST04','6')
/* Thêm vào thuộc tính GHICHU có kiểu dữ liệu varchar(20) cho quan hệ SANPHAM */
ALTER TABLE SANPHAM
ADD GHICHU VARCHAR(20)

/* Thêm vào thuộc tính LOAIKH có kiểu dữ liệu là tinyint cho quan hệ KHACHHANG*/
ALTER TABLE KHACHHANG
ADD LOAIKH TINYINT

/*Sửa kiểu dữ liệu của thuộc tính GHICHU trong quan hệ SANPHAM thành varchar(100).*/
ALTER TABLE SANPHAM
ALTER COLUMN GHICHU VARCHAR(100)

/*Xóa thuộc tính GHICHU trong quan hệ SANPHAM.*/
ALTER TABLE SANPHAM
DROP COLUMN GHICHU

/*Làm thế nào để thuộc tính LOAIKH trong quan hệ KHACHHANG có thể lưu các giá trị là: “Vang
lai”, “Thuong xuyen”, “Vip”, …*/
ALTER TABLE KHACHHANG
ALTER COLUMN LOAIKH VARCHAR(20)

/*Đơn vị tính của sản phẩm chỉ có thể là (“cay”,”hop”,”cai”,”quyen”,”chuc”)*/
ALTER TABLE SANPHAM
ADD CONSTRAINT SANPHAM_DVT CHECK (DVT = 'cay' OR DVT = 'hop' OR DVT = 'cai' OR DVT = 'quyen' OR DVT = 'chuc')

/*Giá bán của sản phẩm từ 500 đồng trở lên*/
ALTER TABLE SANPHAM 
ADD CONSTRAINT SANPHAM_GIA CHECK (GIA > 500)


/*Mỗi lần mua hàng, khách hàng phải mua ít nhất 1 sản phẩm*/
ALTER TABLE CTHD
ADD CONSTRAINT CTHD_SL CHECK (SL >= 1)
/*Ngày khách hàng đăng ký là khách hàng thành viên phải lớn hơn ngày sinh của người đó*/

ALTER TABLE KHACHHANG
ADD CONSTRAINT KHANGHANG_NDK_NGSINH CHECK (NGDK > NGSINH)

nguyenhuuca · October 11, 2015, 4:39am

INSERT INTO SANPHAM
VALUES ('ST08',' Bong bang','Viet Nam','cai','1,000')
INSERT INTO SANPHAM

thứ tự thứ 3 là DVT mà insert ‘VIET NAM’ thì sai la đúng roài, em check lại hết đi nhé

Chaylok_C_u · October 11, 2015, 10:06am

a nhầm . Cám ơn ,chỉ e xóa dữ liệu dòng này đi anh.

INSERT INTO SANPHAM
VALUES ('ST08',' Bong bang','Viet Nam','cai','1,000')

nguyenhuuca · October 11, 2015, 4:51am

Em phải xóa bản quan hệ nhiêu trước, xóa tất các các dòng ở CTHD có mã SP là ST08

Chaylok_C_u · October 11, 2015, 4:54am

Vâng, cám ơn anh…

Chaylok_C_u · October 11, 2015, 10:07am

DELETE FROM CTHD
WHERE MASP = 'ST08'

nó báo lỗi đối tượng không hợp lệ

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘CTHD’.

nguyenhuuca · October 11, 2015, 5:15am

Lân sau nhớ seach google nhé, mấy lỗi này copy là ra ah
thêm cái này trước file

use QLBH
go

ref http://blog.sqlauthority.com/2012/08/31/sql-server-error-fix-msg-208-invalid-object-name-dbo-backupset-invalid-object-name-dbo-backupfile/

Chaylok_C_u · October 11, 2015, 5:29am

Cám ơn anh nhiều, lần sau rút exe.