Re: Problem with enforcement of a 'UNIQUE'

In article nic_at_pipe4.pipeline.com, abarbour_at_pipeline.com (Aaron Barbour) writes:
>In comp.databases.oracle jb2_at_qdot.qld.gov.au (John Blackburn) said:
>
>>Carolyn Giberson (giber_at_pollux.cs.uga.edu) wrote:
>>
>>> I'm working on an object-oriented front-end for Oracle as a thesis
>>> project and am observing curious behavior with enforcement of a
>>> 'UNIQUE' constraint on an attribute. Here is a short description of
>>> the behavior:
 

>>> If I create a table "by hand" like so:
>>> create table t1
>>> (n number UNIQUE NOT NULL);
 

>>> and attempt to insert records with duplicate values for 'n', Oracle
>>> complains about a constraint violation, as it should.
 

>>> If, however, I have my program construct the identical table creation
>>> command and send to Oracle, the table is apparently created without
>>> the 'UNIQUE' constraint. I can insert records with duplicate values
>>> for 'n', and Oracle does *not* complain.
 

>>> Can anyone suggest why I am observing this difference in behavior, and
>>> how I might persuade Oracle to enforce this constraint?
>>
>>Try creating a unique index on the unique column:
>>
>>create unique index x1 on t1 (n);
>>
>
>Before Oracle7, you would need to create an index to enforce the uniqueness
>of the column, as above. If you are using 7.x, then Oracle _should_ create
>the index for you as a result of specifying UNIQUE for the column.
>
>Someone please correct me if I'm wrong about this.
>
>
>Aaron Barbour
>abarbour_at_pipeline.com
>Infolution, Inc.
>New York, NY
>212.978.0081

I don't know *how* your program is creating the table, but if the statement is executing I'd be surprised if it didn't have the constraint applied - it may be disabled, but I'm sure it is there. Unique Contraints are preferable to simply Unique Indexes for a variety of reasons.

Simply: select CONSTRAINT_NAME, STATUS

	 from user_constraints 
	where table_name='T1';

If not then: alter table T1 enable constraint SYS_C<seq#>; which will both enable the constraint *and* build a Unique Index!

Take care.
_

Sincerely,

Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1B-23
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216