Re: Test if Data for a Field is UNIQUE

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 23 Jan 2006 09:17:34 -0500
Message-ID: <IKKdnVQiorQceEnenZ2dnUVZ_sidnZ2d@comcast.com>

"Michael42" <melliott42_at_yahoo.com> wrote in message news:1138023122.199034.154030_at_g47g2000cwa.googlegroups.com...
> 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?
>
> Thanks,
>
> Michael42
>

If LOCATION_KEY is delared as a PK or a UK, then you can use it as a target for an FK constraint (PK preferred).

If you have not yet declared the column as a PK, adding the PK constraint will let you know if the current values are unique, and you can use the EXCEPTIONS INTO clause to get a list of any violations.

++ mcs Received on Mon Jan 23 2006 - 08:17:34 CST