Re: How to determine uniqueness in PL/SQL

In article <373A5B87.D6D2A087_at_dpiwe.tas.gov.au>,   Ruiping Gao <ruiping_at_dpiwe.tas.gov.au> wrote:
> I have a table which has combination of columns as the table's primary
> key. When ever people want to insert records into table, I would like
> create a trigger to check if it is duplicated records according to my
> combination primary key. If it's duplicated records, the system will
> refuse to accept the records, otherwise the new records can be in. But
> I don't know how to do it? Please help.
>

Ruiping;
  If your primary key is defined to Oracle as the primary key to the table, then place an exception block in the insert code and check for the "DUP_VAL_ON_INDEX" exception. This will work on any UNIQUE index. A trigger is not required for this case.

  If you have duplicated "keys" in the table, then you need to remove these duplicated rows. Depending on the table size (number of rows), a simple sql statement may work, or you may need to write a PL*SQL program to remove the duplicates.

The SQL would look like this:
DELETE FROM table_a outer WHERE ({key combination}, ROWID, NOT IN

    (SELECT {key combination}, MIN(ROWID) FROM table_a      GROUP BY {key combination})

There a many different ways of writing this delete query, the "best" depends on table size and available machine resource.

The above is assuming that there are no dups in the table.

HTH
    James

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Thu May 13 1999 - 08:03:02 CDT