Re: Avoiding Duplicate Inserts

From: Chris ( Val ) <chrisval_at_bigpond.com.au>
Date: 20 Nov 2005 20:04:31 -0800
Message-ID: <1132545871.564397.153620@z14g2000cwz.googlegroups.com>

raghu wrote:
> Hi,
>
> I am trying to check if a primary key already exists in a table
> (Oracle 9i) having a few millions of records. Which is the best way to
> check that and avoid duplicate inserts?

You could check the CONSTRAINT_TYPE for a specific table:

SQL> desc user_constraints;

 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                            NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                  NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                           VARCHAR2(1)
 TABLE_NAME                       NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                          LONG
 R_OWNER                                   VARCHAR2(30)
 R_CONSTRAINT_NAME                         VARCHAR2(30)
 DELETE_RULE                               VARCHAR2(9)
 STATUS                                    VARCHAR2(8)
 DEFERRABLE                                VARCHAR2(14)
 DEFERRED                                  VARCHAR2(9)
 VALIDATED                                 VARCHAR2(13)
 GENERATED                                 VARCHAR2(14)
 BAD                                       VARCHAR2(3)
 RELY                                      VARCHAR2(4)
 LAST_CHANGE                               DATE
 INDEX_OWNER                               VARCHAR2(30)
 INDEX_NAME                                VARCHAR2(30)
 INVALID                                   VARCHAR2(7)
 VIEW_RELATED                              VARCHAR2(14)

Cheers,
Chris Val Received on Sun Nov 20 2005 - 22:04:31 CST