Re: Test if Data for a Field is UNIQUE

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Mon, 23 Jan 2006 09:31:08 -0500
Message-ID: <MM5Bf.2972$yb2.2812@news.itd.umich.edu>

Michael42 wrote:
> Hello,
>
> In Oracle 9i R2 on Solaris 8 I have a table (LOCATION) that has data.
> I want to create a Foreign Key (FK) and base it on an existing field
> (location_key) in this table. I am not 100% sure the values are unique
> (which is required to make it a FK for a child table).
>
> Can someone please share SQL that can be used to determine if the
> values in my LOCATION table field location_key field are UNIQUE?

The easiest way is to simply try to put a unique index on the field and see if it works. If you can add the unique index without an error, you don't have any repetitions. Note that you won't be able to create the FK until you have a uniqueness constraint on the field in the parent table.

A read-only approach is
SELECT * from location loc1, location loc2 where loc1.location_key=loc2.location_key and loc1.rowid < loc2.rowid

//Walt Received on Mon Jan 23 2006 - 08:31:08 CST