Prevent Duplicate Insert Data in Oracle

Question

i have table COBA with 3 fields, they are NOMER, NAMA, KETERANGAN. in table there are exist data like this:

i wanna insert another values to that table, but how can i prevent duplicate insert query data in oracle? i have tried use WHERE NOT EXIST, this is my query:

INSERT INTO COBA(NOMER, NAMA, KETERANGAN)
(SELECT '2','FIAN', 'JEKULO'
FROM DUAL
WHERE NOT EXISTS (SELECT * FROM COBA WHERE NOMER='1' AND NAMA='AMIN' AND KETERANGAN='JEPARA'))

that query didn't work....any suggest for me,...thanks...

Create a unique constraint on the fields: http://www.techonthenet.com/oracle/unique.php. If `NOMER` is the primary key then you'd create a PK on `NOMER` and a unique constraint on the other two. If you create it on all three then you can have a duplicate `NOMER` — Nick.Mc, Jan 07 '16 at 01:17
are there any another way to solve this without Create a unique constraint? — aminvincent, Jan 07 '16 at 01:23
yes, make sure the subquery actually checks for the fields you're inserting... ps: that NOMER column is suspicious. If it's a number, pass numbers. — Sebas, Jan 07 '16 at 01:34
Is there a reason you don't want to use a unique constraint? 99% of database programmers think they're a good idea. They also help with performance because they give the query planner hints about the data. — Nick.Mc, Jan 09 '16 at 05:53
@Nick.McDermaid you're right that using unique constraint is the best choice to prevent duplicate value,... but the reason why i don't use it because it appear `ORA-00001: unique constraint (PAYROLL.COBA_UNIQUE) ` violated appear when i run same value and there is a error notif,... i don't user show that error when insert data to my project.. — aminvincent, Jan 11 '16 at 01:02
That's what error handlers are for. You capture and handle that error properly in your application (whatever it may be) and show a suitable error message or take appropriate action in your application. You don't just echo an unhelpful error message to a user. — Nick.Mc, Jan 11 '16 at 01:12
i dont wanna user see the error when insert duplicate data,.. and it just skip inserting when there are some duplicate value — aminvincent, Jan 11 '16 at 01:26

score 3 · Answer 1 · answered Jan 07 '16 at 01:19

3

Use a unique constraint:

ALTER TABLE COBA ADD CONSTRAINT uni_c UNIQUE (NOMER, NAMA, KETERANGAN)

answered Jan 07 '16 at 01:19

Tim Biegeleisen

502,043
27
286
360

are there any another way to solve this without Create a unique constraint? – aminvincent Jan 07 '16 at 01:23
You could create a custom function or trigger to check, but long term you should add the constraint if you foresee this happening again in the future. – Tim Biegeleisen Jan 07 '16 at 01:28
if i create unique constraint,..so how about the query to insert data in oracle? it's same like insert query generally? – aminvincent Jan 07 '16 at 01:39
Yes just `INSERT` as you normally would. Keep in mind that if you violate the constraint you will get an error. If you want to ignore the constraint for a certain `INSERT`, there might be a [way to do this](http://stackoverflow.com/questions/9332360/oracle-equivalent-to-mysql-insert-ignore). – Tim Biegeleisen Jan 07 '16 at 01:45
finally i use unique constraint ,..but skip error when data exist still doesn't work yet,..i follow you link but difficult me to implement it. – aminvincent Jan 11 '16 at 02:12

Utsav · Accepted Answer · 2016-01-07T02:06:04.793

1

If you dont wanna use Unique constraint, you can you left join while inserting to check if the nomer exists in target or not like below. By this method you will not get error even if the record already exists in your table. It will just be skipped.

insert into coba
(select s.nomer,s.nama,s.ket from 
    (select 1 as nomer,'AA' as nama,'bb' as ket from dual) s
left join 
    coba t
on s.nomer=t.nomer
    where t.nomer is null
);

I created a fiddle in MySQL (as Oracle is not working) but the functionality would be same. As you can see in example below, the nomer =1 is not inserted again.

See fiddle demo here

http://sqlfiddle.com/#!2/3add2/1

edited Jan 07 '16 at 02:06

answered Jan 07 '16 at 02:00

Utsav

7,914
2
17
38

it worked,..thanks,...but how can i prevent duplicate more thank a filed in query like nomer = 1, nama= amin and keterangan = jepara? – aminvincent Jan 07 '16 at 06:27
i got it sir,... just adding condition in after WHERE and it solved,.. many thanks – aminvincent Jan 07 '16 at 06:34
if NOMER is primary key and it is sequence,..how to solve this? – aminvincent Jan 07 '16 at 10:25
If it is sequence, then you will get a new value every time so you dont need to worry about duplicates. Else you can use the same logic (using left join) to find out if it already exists in table or not. – Utsav Jan 07 '16 at 10:41
if i add sequence in NOMER i got error like this ORA-02287: sequence number not allowed here,...any suggest? – aminvincent Jan 09 '16 at 02:09

score 0 · Answer 3 · answered Nov 25 '22 at 14:45

Use a MERGE statement (which will only query the source and destination tables once):

MERGE INTO COBA dst
USING (
  SELECT '2' AS nomer, 'FIAN' AS nama, 'JEKULO' AS keterangan FROM DUAL 
) src
ON (
    dst.nomer      = src.nomer
AND dst.nama       = src.nama
AND dst.keterangan = src.keterangan
)
WHEN NOT MATCHED THEN
  INSERT (NOMER, NAMA, KETERANGAN)
  VALUES (src.nomer, src.nama, src.keterangan);

Prevent Duplicate Insert Data in Oracle

3 Answers3

Linked