ORA-00001: unique constraint (SYSTEM.BROWSERS_PK) violated
I am working on a C # project where I am trying to insert a record using the following SQL for an Oracle database:
String Qry = INSERT INTO browsers (browsers.browser,browsers.engine,browsers.platform,browsers.version,browsers.grade) VALUES ('Alans browser','Gecko','every','1.0','U') RETURNING id INTO :ID
I am following the example code: http://www.sqlines.com/oracle-to-sql-server-cs-conversion/insert-returning-clause
My code looks like this:
OracleConnection conn = new OracleConnection(...);
OracleCommand cmd = null;
...
cmd = new OracleCommand(Qry, conn);
OracleParameter prm = new OracleParameter();
prm = new OracleParameter(":ID", OracleDbType.Int32, ParameterDirection.ReturnValue);
cmd.Parameters.Add(prm);
cmd.ExecuteNonQuery(); //this line throws error
query.setNewRecordID(cmd.Parameters[":ID"].Value.ToString());
...
Naturally, the id column in the browsers table is set with a sequence and a trigger to auto-increment on any insert. I don't understand why I am getting the error when I am not trying to set the id column. I am just trying to get the id value given to the new record.
Uddate: Following are the SQL commands for creating sequence and trigger:
Sequence:
CREATE SEQUENCE "SYSTEM"."BROWSERS_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOPARTITION ;
Trigger:
create or replace trigger BROWSERS_TRG
before insert on "SYSTEM"."BROWSERS"
for each row
begin
if inserting then
if :NEW."ID" is null then
select BROWSERS_SEQ.nextval into :NEW."ID" from dual;
end if;
end if;
end;
source to share
I think there is a problem with your sequence (I couldn't compile as is). I took off NOPARTITION and it worked.
In my SCOTT schema (sample), I created a similar scenario as follows:
SCOTT@dev> CREATE TABLE "SCOTT"."EMP2"
2 (
3 "EMPNO" NUMBER(4,0),
4 "ENAME" VARCHAR2(10 BYTE),
5 "JOB" VARCHAR2(9 BYTE),
6 "MGR" NUMBER(4,0),
7 "HIREDATE" DATE,
8 "SAL" NUMBER(7,2),
9 "COMM" NUMBER(7,2),
10 "DEPTNO" NUMBER(2,0)
11 )
12 TABLESPACE "SYSTEM" ;
Table created.
SCOTT@dev> CREATE UNIQUE INDEX "SCOTT"."EMP2_EMPNO" ON "SCOTT"."EMP2"
2 (
3 "EMPNO"
4 )
5 TABLESPACE "SYSTEM" ;
Index created.
SCOTT@dev> set define off;
SCOTT@dev> ALTER TABLE "SCOTT"."EMP2" ADD PRIMARY KEY ("EMPNO");
Table altered.
SCOTT@dev> CREATE SEQUENCE EMP2_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;
Sequence created.
SCOTT@dev> CREATE OR REPLACE TRIGGER EMP2_TRG before
2 INSERT ON SCOTT.EMP2 FOR EACH row BEGIN IF inserting THEN IF :NEW.EMPNO IS NULL THEN
3 SELECT EMP2_SEQ.NEXTVAL INTO :NEW.EMPNO FROM dual;
4 END IF;
5 END IF;
6 END;
7 /
Trigger created.
SCOTT@dev> commit;
Commit complete.
SCOTT@dev> INSERT INTO EMP2
2 (ENAME)
3 VALUES
4 ('FRED')
5 /
1 row created.
SCOTT@dev> commit;
Commit complete.
Hence, consistency appears to be the culprit.
source to share
Are you sure the sequence and trigger are set correctly? I usually don't use a trigger, just the sequencer.nextval file directly in the request. If you need to use the generated id in another part of the code, you can execute "select sequence.nextval from dual" and cache the results
source to share