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?
-
It's not clear what you are trying to do. If you are wondering how to fill the id column, you should use a sequence.pgb– pgb2009-10-31 18:19:39 +00:00Commented Oct 31, 2009 at 18:19
-
you need to be more specific. as in: "this is the query (simplified) that i am trying to commit but it generates duplicate rows [query]"mga– mga2009-10-31 18:21:48 +00:00Commented Oct 31, 2009 at 18:21
Add a comment
|
3 Answers
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.
Comments
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).
Comments
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.
