在SQL SERVER 7.0里,我写的SQL运行之后有一个Cannot insert duplicate key in object的错误,怎么找也找不出,谁能改出这个错误?
错误信息:
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UKRECEIVED'. Cannot insert duplicate key in object 'DeliveryItem'.
The statement has been terminated.
==================================
CREATE TABLE Supplier (
SupplierID smallint NOT NULL,
SupplierName char(25) NOT NULL,
CONSTRAINT PKSID PRIMARY KEY (SupplierID),
CONSTRAINT UKSN UNIQUE (SupplierName),
);
INSERT INTO Supplier(SupplierID,SupplierName) VALUES
(1,'IBM');
INSERT INTO Supplier(SupplierID,SupplierName) VALUES
(2,'Microsoft');
INSERT INTO Supplier(SupplierID,SupplierName) VALUES
(3,'Oracle');
INSERT INTO Supplier(SupplierID,SupplierName) VALUES
(4,'Informix');
CREATE TABLE StockItem (
StockItemID smallint NOT NULL,
[Description] varchar(30) NOT NULL,
SupplierID smallint NOT NULL,
ProductCode char(25) NOT NULL,
StockHolding smallint NOT NULL,
CONSTRAINT PKSIID PRIMARY KEY (StockItemID),
CONSTRAINT FKSID FOREIGN KEY (SupplierID)
REFERENCES Supplier(supplierid),
CONSTRAINT UKDES UNIQUE ([Description]),
CONSTRAINT UKPCODE UNIQUE (ProductCode),
CONSTRAINT UKSH UNIQUE (StockHolding)
);
INSERT INTO
StockItem(StockItemID,
[Description],SupplierID,ProductCode,StockHolding) VALUES
(1,'DB2 Universal Database',1,'20P5265',47);
INSERT INTO
StockItem(StockItemID,
[Description],SupplierID,ProductCode,StockHolding) VALUES
(2,'Microsoft SQL Server',2,'228-01079',37);
INSERT INTO
StockItem(StockItemID,
[Description],SupplierID,ProductCode,StockHolding) VALUES
(3,'Oracle Database',3,'A90606',11);
INSERT INTO
StockItem(StockItemID,
[Description],SupplierID,ProductCode,StockHolding) VALUES
(4,'Microsoft Access 2002',2,'077-02012',33);
CREATE INDEX FKSID ON Supplier(SupplierID);
CREATE TABLE [Order] (
OrderID int NOT NULL CHECK (len(convert(char
(10),OrderID))<6),
OrderDate datetime NOT NULL,
SupplierID smallint NOT NULL,
CONSTRAINT PKOIDanother PRIMARY KEY (OrderID),
CONSTRAINT FKSIDanother FOREIGN KEY (SupplierID)
REFERENCES Supplier(supplierid),
CONSTRAINT UKODATE UNIQUE (OrderDate)
);
INSERT INTO [Order](OrderID,OrderDate,SupplierID) VALUES
(10001,'12/10/2002',2);
INSERT INTO [Order](OrderID,OrderDate,SupplierID) VALUES
(10002,'12/12/2002',1);
INSERT INTO [Order](OrderID,OrderDate,SupplierID) VALUES
(10003,'12/14/2002',3);
CREATE INDEX FKSIDs ON Supplier(SupplierID);
CREATE TABLE OrderItem (
OrderItemID smallint NOT NULL,
OrderID int NOT NULL CHECK (len(convert(char
(10),OrderID))<6),
StockItemID smallint NOT NULL,
Requested smallint NOT NULL,
Received smallint NOT NULL,
CONSTRAINT PKOIID PRIMARY KEY (OrderItemID),
CONSTRAINT FKOIDe FOREIGN KEY (OrderID) REFERENCES
[Order](Orderid),
CONSTRAINT FKSIID FOREIGN KEY (StockItemID) REFERENCES
[stockitem](stockitemid),
CONSTRAINT UKODATE2 UNIQUE (Requested),
CONSTRAINT UKODATE23 UNIQUE (Received)
);
INSERT INTO OrderItem
(OrderItemID,OrderID,StockItemID,Requested,Received)
VALUES
(1,10001,2,30,30);
INSERT INTO OrderItem
(OrderItemID,OrderID,StockItemID,Requested,Received)
VALUES
(2,10001,4,40,31);
INSERT INTO OrderItem
(OrderItemID,OrderID,StockItemID,Requested,Received)
VALUES
(3,10002,1,20,21);
INSERT INTO OrderItem
(OrderItemID,OrderID,StockItemID,Requested,Received)
VALUES
(4,10003,3,60,230);
CREATE INDEX FKOID ON [Order](OrderID);
CREATE INDEX FKSIID ON StockItem(StockItemID);
CREATE TABLE Delivery (
DeliveryID smallint NOT NULL,
DeliveryDate datetime NOT NULL,
SupplierID smallint NOT NULL,
Consignment char(25) NOT NULL,
CONSTRAINT PKDLRYID PRIMARY KEY (DeliveryID),
CONSTRAINT PKDLRYDATE UNIQUE (DeliveryDate),
CONSTRAINT FKSID3 FOREIGN KEY (SupplierID) REFERENCES
supplier(supplierid),
CONSTRAINT UKCON UNIQUE (Consignment)
);
SELECT * FROM Delivery
INSERT INTO Delivery
(DeliveryID,DeliveryDate,SupplierID,Consignment)
VALUES
(1,'07/11/2002',2,'NM-FG567-UI5');
INSERT INTO Delivery
(DeliveryID,DeliveryDate,SupplierID,Consignment)
VALUES
(2,'1/12/2002',1,'V978HJK-2');
INSERT INTO Delivery
(DeliveryID,DeliveryDate,SupplierID,Consignment)
VALUES
(3,'1/16/2002',3,'BJP-692-FGH8');
CREATE INDEX FKSIDw ON Supplier(SupplierID);
CREATE TABLE DeliveryItem (
DeliveryItemID smallint NOT NULL,
DeliveryID smallint NOT NULL,
OrderItemID smallint NOT NULL,
Received smallint NOT NULL,
CONSTRAINT PKDIID PRIMARY KEY (DeliveryItemID),
CONSTRAINT FKDLRYID FOREIGN KEY (DeliveryID)
REFERENCES delivery(deliveryid),
CONSTRAINT FKOIID FOREIGN KEY (OrderItemID)REFERENCES
orderitem(orderitemid),
CONSTRAINT UKRECEIVED UNIQUE (Received)
);
INSERT INTO DeliveryItem
(DeliveryItemID,DeliveryID,OrderItemID,Received)
VALUES
(1,1,1,30);
INSERT INTO DeliveryItem
(DeliveryItemID,DeliveryID,OrderItemID,Received)
VALUES
(2,1,2,30);
INSERT INTO DeliveryItem
(DeliveryItemID,DeliveryID,OrderItemID,Received)
VALUES
(3,2,3,20);
INSERT INTO DeliveryItem
(DeliveryItemID,DeliveryID,OrderItemID,Received)
VALUES
(4,3,4,20);
CREATE INDEX FKDLRYID ON Delivery(DeliveryID);
CREATE INDEX FKOIID ON OrderItem(OrderItemID);