Re: Test if Data for a Field is UNIQUE

On Mon, 23 Jan 2006 05:32:02 -0800, 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?
>
> Thanks,
>
> Michael42

This is an ages old question. Here is how you do it:

1. ALTER TABLE EMP ADD CONSTRAINT EMP_SAL_UK UNIQUE(SAL); SQL> @?/rdbms/admin/utlexcpt

Table created.
SQL> alter table emp enable constraint emp_sal_uk exceptions into exceptions; alter table emp enable constraint emp_sal_uk exceptions into exceptions *
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.EMP_SAL_UK) - duplicate keys found

SQL> The exceptions table looks like this:
SQL> desc exceptions

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT                                         VARCHAR2(30)

All the rowids of the offending rows will be listed in the table. BTW, you don't need unique index to have a unique constraint. You can have a unique constraint on the column even the values in the column are not unique.

-- 
http://www.mgogala.com