Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid duplicate rows from being inserted in the table.
How to avoid duplicate rows from being inserted in the table. [message #436004] Mon, 21 December 2009 00:51 Go to next message
vicks_vj@yahoo.com
Messages: 3
Registered: December 2009
Junior Member
Hi all,

I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table

transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)

I want to write SQL which should not inset duplicate record.
e.g.

I have written one as bellow:

insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;

Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)

Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.

Suggestions Plz...
Edit/Delete Message
Re: How to avoid duplicate rows from being inserted in the table. [message #436006 is a reply to message #436004] Mon, 21 December 2009 01:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
New to database?

UNIQUE constraint can restrict duplicate values in the table...
Just search for it on Google.

regards,
Delna
Re: How to avoid duplicate rows from being inserted in the table. [message #436007 is a reply to message #436006] Mon, 21 December 2009 01:12 Go to previous messageGo to next message
vicks_vj@yahoo.com
Messages: 3
Registered: December 2009
Junior Member
Hey Delna,

Thanks for quick reply. But very first statement i wrote is "I don't want to put any unique constraint".

I am expecting some SQLs from u ppl. Hope u don't mind. Smile
Re: How to avoid duplicate rows from being inserted in the table. [message #436010 is a reply to message #436004] Mon, 21 December 2009 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can use "not exists" (if you never insert null values) but why don't you want to create a constraint which is the oorrect way to do it as any insert statement will NOT guarantee you the uniquess, only the uniquess from the point of view of the current query/transaction.

Regards
Michel
Re: How to avoid duplicate rows from being inserted in the table. [message #436012 is a reply to message #436007] Mon, 21 December 2009 01:34 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
as Delna said please use the Unique constraint , if you use exists/not exists then you have to write select statement to check existing record this will lead to performance issue [ as before every insert you have to go for select ]

so if you use unique constraints Server will perform the unique check task for you..so just trap the error raise by Server and rollback the transaction.

Regards,
Rahul
Re: How to avoid duplicate rows from being inserted in the table. [message #436015 is a reply to message #436012] Mon, 21 December 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, it is worse than performances problem, it simply DOES NOT WORK as soon as there is more than one session working on the same object.

Regards
Michel
Re: How to avoid duplicate rows from being inserted in the table. [message #436016 is a reply to message #436015] Mon, 21 December 2009 01:49 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
I Agree Michel.
Razz
Re: How to avoid duplicate rows from being inserted in the table. [message #436019 is a reply to message #436007] Mon, 21 December 2009 02:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vicks_vj@yahoo.com wrote on Mon, 21 December 2009 08:12
Hey Delna,

Thanks for quick reply. But very first statement i wrote is "I don't want to put any unique constraint".

I am expecting some SQLs from u ppl. Hope u don't mind. Smile

Quick answer: You cannot.
There is no safe or sane way to do this and keep your application usable for multiple sessions.
Re: How to avoid duplicate rows from being inserted in the table. [message #436021 is a reply to message #436007] Mon, 21 December 2009 02:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
But very first statement i wrote is "I don't want to put any unique constraint".


I don't see any statement like this, or specifying about UNIQUE constraint in OP. ./fa/1987/0/

regards,
Delna
Re: How to avoid duplicate rows from being inserted in the table. [message #436025 is a reply to message #436015] Mon, 21 December 2009 03:00 Go to previous messageGo to next message
vicks_vj@yahoo.com
Messages: 3
Registered: December 2009
Junior Member
Michel,

I am not creating any constraint on this table because, I want to check for unique records only in certain operations. For others, I don't care if it inserts duplicate records.

The information inserted in the table is user specific. Nobody else is allowed to insert/update somebody else' information. So I guess, session should not be concern here.

Will the following query work correctly?

insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
not exists (select 1 from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12);



Re: How to avoid duplicate rows from being inserted in the table. [message #436032 is a reply to message #436025] Mon, 21 December 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will the following query work correctly?

If I say yes and it will not, who will be blamed?
If I say no then what will you put in your program?

So no answer can answer your problem, you have to test it by yourself.

Regards
Michel

[Updated on: Mon, 21 December 2009 03:10]

Report message to a moderator

Re: How to avoid duplicate rows from being inserted in the table. [message #436097 is a reply to message #436025] Mon, 21 December 2009 08:42 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You could very well create a unique index assuming the table is having a primary key and cater only for this particular scenario.

I will leave it to you find out how to do it. As others have already mentioned unique index is the only way to be certain that you don't end up with duplicate values. In software programming always expect the unexpected.

Regards

Raj
Re: How to avoid duplicate rows from being inserted in the table. [message #436316 is a reply to message #436097] Tue, 22 December 2009 21:55 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Quote:

insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
not exists (select 1 from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)


Why you are Against Unique Constraint ? even you use "Exists" you are going back to the Table level to check if value exist or not !!! if you use query like this it is going to hit the performance Badly.

Regards,
Rahul

Re: How to avoid duplicate rows from being inserted in the table. [message #436361 is a reply to message #436316] Wed, 23 December 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you repeat the same thing? You already said that in a previous answer and I already answered you this is not the worst thing.

Regards
Michel
Re: How to avoid duplicate rows from being inserted in the table. [message #436835 is a reply to message #436004] Mon, 28 December 2009 13:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
So my turn.

OP, you are working inside a relational database. Additionally you are working with Oracle and it has its own quirks like any other database. To that end I have comments:

1) if your data has a uniqueness about it then you should always use a unique constraint to enforce it. This has nothing to do with the code you write and everything about documenting what your data means and enforcing that meaning across everyone who uses the data. So.. unless you have some very special situation, you should get your DBA to create a unique constraint for your data. Do not bother to reply to this with some rebuttal, you would only be wrong. A unique constraint should be created (unless you have some outstanding situation, in which case I surely want to hear about it). Either your data has meaning or it does not. If your data has no meaning then whatever code you write for this data is wrong anyway because I will always be able to find some way to break it by assuming my own meaning on your data which I can do since you never said I could not because you never created a unique constraint to the contrary.

2) you are free to write what ever code you want once the unique constraint is in place. If you want to write an insert statement smart enough to avoid inserting rows that already exist, go ahead. In fact, I would encourage it. It is invariably better to write code that does not produce errors than to write code that does. I applaud your efforts therefore to write an insert statement that avoids inserting duplicates. Using EXISTS is often a good choice if you need a starting direction.

3) there is a new feature in Oracle that would allow you to skip duplicate rows once you create the UNIQUE index; ERROR LOG TABLES. See if this helps you as well.

Good luck, Kevin
Re: How to avoid duplicate rows from being inserted in the table. [message #436840 is a reply to message #436004] Mon, 28 December 2009 15:30 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
remember a unique condition can be on more then one column. For example you could have the unique on all three columns of

create unique index my_table_u1 on my_table(txn_date,payee,amount);

Re: How to avoid duplicate rows from being inserted in the table. [message #436924 is a reply to message #436004] Tue, 29 December 2009 10:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Sure, but tell me, what is the difference between

CREATE UNIQUE INDEX ...

and

ALTER TABLE ADD CONSTRAINT T_UK1 UNIQUE ...

Creating a unique index is not the same as creating a unique constraint. You can go ahead and create the unique index if you like and indeed I always do, but you should always follow it up with a unique constraint.

Kevin
Re: How to avoid duplicate rows from being inserted in the table. [message #436941 is a reply to message #436004] Tue, 29 December 2009 11:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Kevin,
I was simply letting him know (if he didn't already) that a unique is not constrained to a single column. and a unique constraint creates a unique index anyway.

[Updated on: Tue, 29 December 2009 11:55]

Report message to a moderator

Re: How to avoid duplicate rows from being inserted in the table. [message #436944 is a reply to message #436004] Tue, 29 December 2009 12:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Bill. I figured same. I took your post only as an opportunity for me to offer up a topic to the OP to think about.

Kevin
Re: How to avoid duplicate rows from being inserted in the table. [message #514765 is a reply to message #436944] Wed, 06 July 2011 06:51 Go to previous messageGo to next message
hais
Messages: 2
Registered: July 2011
Location: Karnataka
Junior Member
Hi,
I also have a similar situation, would like to know the better way to avoid duplicates in a table where there is no unique constraint.
The situation is as follows. There is one column as Item_Id and another column as Item_status together with around 40 other columns in my table. For a particular item id there can be multiple records with status 'PENDED', but can have only one record with status 'ACTIVE'
In this case I cannot create a constraint on Item_Id + status, So how can I restrict duplicate records inserting for an item_id with more than one 'ACTIVE' status.
Re: How to avoid duplicate rows from being inserted in the table. [message #514766 is a reply to message #514765] Wed, 06 July 2011 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One way:
SQL> create table t (id integer, status varchar2(10));

Table created.

SQL> create unique index t_i on t (decode(status,'PENDED',null,id), nullif(status,'PENDED'));

Index created.

SQL> insert into t values(1,'ACTIVE');

1 row created.

SQL> /
insert into t values(1,'ACTIVE')
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.T_I) violated


SQL> insert into t values(1,'PENDED');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> select * from t;
        ID STATUS
---------- ----------
         1 ACTIVE
         1 PENDED
         1 PENDED
         1 PENDED

4 rows selected.

Regards
Michel

[Updated on: Wed, 06 July 2011 07:03]

Report message to a moderator

Re: How to avoid duplicate rows from being inserted in the table. [message #514771 is a reply to message #514766] Wed, 06 July 2011 07:17 Go to previous message
hais
Messages: 2
Registered: July 2011
Location: Karnataka
Junior Member
Thank you so much for the quick reply
Previous Topic: Help needed while working with composite primary keys
Next Topic: How we can execute .ksh from Oracle8i Pl/sql Code(2 Merged)
Goto Forum:
  


Current Time: Thu Dec 08 22:11:56 CST 2016

Total time taken to generate the page: 0.10716 seconds