Re: Unique index vs. unique constraint

Hello.

        I think you will probably want to use a sequence if I am understanding your question. You would create a sequence we'll call COMPANY_SEQ and then get the 'nextval' from that when you want to insert into your COMPANY table, like this:

insert into company
(select company_seq.nextval, 'My Company', '555 Malibu Dr.' from dual)

Actually, in today's world, that company would go nowhere unless it was named 'My Company.com' - ah, that's better - now it'll make millions. :)

As far as the parent key violated error is concerned, maybe your table is the child table in a relationship with another table and its parent record does not exist in that other table when you try to do your insert. Give us the exact error message/error number and we'll be able to help you more if this doesn't do it.

Hope this helps,
-Jeff

On Tue, 14 Sep 1999 17:12:18 GMT, adholaki_at_my-deja.com wrote:

>What is the SQL syntax for inserting a value into a column with a
>unique constraint/ index?
>
>ie.
>
>INSERT into Company (id, name, address)
>
>VALUES (56, someCompany, 555 malibu dr.)
>
>here, the id column has a unique constraint and is not linked to any
>other table. the above statement results in a parent key violated or
>some other error.
>
>the keyword "nextval" was mentioned, but i don't have any details on
>this.
>
>thanks
>
>
>
>
>In article <37CD0E1C.78F9_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
>> Richard Gaston wrote:
>> >
>> > I think unique constraints are implemented as unique indexes in
>oracle.
>> > Oracle will create a unique index for the column(s) you declare
>unique in a
>> > constraint. So apparently, there's no real difference between the
>two.
>> >
>> > As to why you'd prefer one over the other, I don't know. Taste?
>> >
>> > Michael Hjorth <michael_hjorth_at_my-deja.com> wrote in message
>> > news:7q2vo5$c0g$1_at_nnrp1.deja.com...
>> > > What is the functional difference between a unique index and a
>unique
>> > > constraint?
>> > > I know the latter has a constraint as well as the index, but
>apparently
>> > > it enforces the same restrictions on the database as the unique
>index.
>> > > Or did I get it wrong?
>> > > Why would I prefer one over the other in a particular case?
>> > >
>> > > Thank you for any comments.
>> > >
>> > > Michael.
>> > >
>> > >
>> > > Sent via Deja.com http://www.deja.com/
>> > > Share what you know. Learn what you don't.
>>
>> It is possible to implement a unique constraint with a NON unique
>index
>> in 8.1, thus making them different...
>>
>> Also with unique constraints you can make them deferrable and other
>nice
>> things that cannot be done with a unique index.
>>
>> Cheers
>>
>> --
>> ===========================================
>> Connor McDonald
>> "These views mine, no-one elses etc etc"
>> connor_mcdonald_at_yahoo.com
>>
>> "Some days you're the pigeon, and some days you're the statue."
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Tue Sep 14 1999 - 12:57:52 CDT