Unique Constraint violation...
627047May 5 2009 — edited May 6 2009Using 9.2 Oracle
Sql Developer
I have the following ddl script.....
CREATE TABLE daab.products
(productid NUMBER(10,0),
productname NVARCHAR2(50),
categoryid NUMBER(10,0),
unitprice NUMBER(19,4),
lastupdate DATE)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
NOMONITORING
/
-- Constraints for DAAB.PRODUCTS
ALTER TABLE daab.products
ADD CONSTRAINT products_constraint UNIQUE (productid)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Triggers for DAAB.PRODUCTS
CREATE OR REPLACE TRIGGER daab.products_productid_trg
BEFORE
INSERT OR UPDATE
ON daab.products
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ProductID IS NULL THEN
SELECT Products_ProductID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
:new.ProductID := v_newVal;
END IF;
END;
/
-- End of DDL Script for Table DAAB.PRODUCTS
and am running this stored procedure
PROCEDURE ADDPRODUCT
(
v_ProductName IN NVARCHAR2 DEFAULT NULL ,
v_CategoryID IN NUMBER DEFAULT NULL ,
v_UnitPrice IN NUMBER DEFAULT NULL ,
v_LastUpdate in date default sysdate,
v_productID in number default null
)
AS
v_temp Number(1, 0) := 0;
BEGIN
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT ProductID
FROM Products
WHERE ProductID = v_productID );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
IF v_temp = 1 THEN
BEGIN
UPDATE Products
SET ProductName = v_ProductName,
CategoryID = v_CategoryID,
UnitPrice = v_UnitPrice,
LastUpdate = v_LastUpdate
WHERE productID = v_productID;
END;
ELSE
BEGIN
INSERT INTO Products
( ProductName, CategoryID, UnitPrice,LastUpdate )
VALUES ( v_ProductName, v_CategoryID, v_UnitPrice,v_LastUpdate );
END;
END IF;
END;
Executing this code:
PROCEDURE ADDPRODUCT
(
v_ProductName IN NVARCHAR2 DEFAULT NULL ,
v_CategoryID IN NUMBER DEFAULT NULL ,
v_UnitPrice IN NUMBER DEFAULT NULL ,
v_LastUpdate in date default sysdate,
v_productID in number default null
)
AS
v_temp Number(1, 0) := 0;
BEGIN
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT ProductID
FROM Products
WHERE ProductID = v_productID );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
IF v_temp = 1 THEN
BEGIN
UPDATE Products
SET ProductName = v_ProductName,
CategoryID = v_CategoryID,
UnitPrice = v_UnitPrice,
LastUpdate = v_LastUpdate
WHERE productID = v_productID;
END;
ELSE
BEGIN
INSERT INTO Products
( ProductName, CategoryID, UnitPrice,LastUpdate )
VALUES ( v_ProductName, v_CategoryID, v_UnitPrice,v_LastUpdate );
END;
END IF;
END;
and that's where I get the contraint error. any ideas why?