How to prevent duplicate rows in insert query using Oracle?

Question

I have table A in Oracle that has a primary key (id). I need to insert data into this table.

How do I prevent duplicate rows?

Answer 1

First you would create a unique id on this table that has a sequence. Then when you insert into that table, in the insert statement you can use:

 tableName_sn.nextval

in a statement like:

inserti into tableName (id) values (tableName_sn.nextval)

to get the next unique key in the sequence. Then if you use this key you will guarantee that it is unique. However, the caveat to that is if someone just entered a key not using the nextval function you will get primary key violations.

Answer 2

If the id column is marked as the PK you will not be able to insert a duplicate key, the server will throw an exception.

If you will insert duplicate data with different key - that's a logic that you need to deal with (like putting a unique constraint on the actual data) or do a check before you insert.

Answer 3

If you mean that you have rows that are identical (apart from the primary key) and you want to know how to delete them then do:

select col2, col3, ...coln, min(id) from A
group by col2, col3, ...coln

(I.e. select on all columns except the id.)

To get the unique instances do

delete from A where id not in
(select min(id) from A
group by col2, col3, ...coln) as x

to delete all rows except the unique instances (i.e. the duplicates).