• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • Last Modified:

Duplicate rows check on Insert or PLSQL block

I want to do a check when inserting data into a table to make sure that there are no duplicate rows

lets say i have 3 columns, when the data 1, 2, 3. if i try inserting that same data, the insert should fail.

what direction should i go in and can someone give some brief examples
1 Solution
slightwv (䄆 Netminder) Commented:
Can you not create a unique index on those 3 columns?

Otherwise you need to seleect those 3 columns before you insert.
Gerwin Jansen, EE MVETopic Advisor Commented:
Create a 3 column unique index I would say.

@slightwv - crosspost...
futureDBAAuthor Commented:
I am not sure what you mean, this is my first time hearing the term.. i will head to google, if you can provide documentation and examples that would be extremely helpful
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

slightwv (䄆 Netminder) Commented:
>>I am not sure what you mean, this is my first time hearing the term

What term?  "Unique index"?
The following is an example of how the unique index on multiple columns will prevent duplicate values for those columns:
create table idxtbl
(id varchar2(1)
,col1 number
,col2 number
,col3 number);

insert into idxtbl values('A',1,2,3);
insert into idxtbl values('B',4,5,6);

create unique index tbl3cols on idxtbl(col1, col2, col3);

insert into idxtbl values('C',1,2,3);
ERROR at line 1:
ORA-00001: unique constraint (SCHEMANAME.TBL3COLS) violated
futureDBAAuthor Commented:
yes, the term unique index. i will look into this now. thank you
slightwv (䄆 Netminder) Commented:
awking00 has already provided a working example.
I've requested that this question be closed as follows:

Accepted answer: 250 points for awking00's comment #a38326303
Assisted answer: 250 points for slightwv's comment #a38325683

for the following reason:

All,<br /><br />I'm splitting the points per Expert's request:<br /><a href="http://www.experts-exchange.com/R_10542.html">http://www.experts-exchang<wbr />e.com/R_10<wbr />542.html</a><br /><br />The original closure included only <a href="http:#a38326303">http:#a38326303</a><br /><br />_alias99<br />Community Support Moderator
futureDBAAuthor Commented:
perfect, thank you for taking so long to close

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now