Re: How to create a uniqueness constraint based on two fields?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 27 Jan 2005 08:50:39 -0800
Message-ID: <1106844483.294123@yasure>

Thomas Kellerer wrote:

> On 26.01.2005 19:33 DA Morgan wrote:
>

>>>CREATE UNIQUE INDEX ON the_table (first_name, last_name);
>>>
>>>should do it, I guess.
>>>
>>>Thomas
>>
>>
>>While your answer is technically correct there is almost not good
>>reason for ever creating a unique index in Oracle. Unique indexes,
>>unlike unique constraints are not as well documented in the data
>>dictionary and can't be deferred.

>
>
> Interesting, I didn't know that.
>
> Could you more be more detailed on what you mean with "can't be deferred"?
>
> Thanks
> Thomas

It is a 1/2 hour plus lecture. But you can find the examples here: http://www.psoug.org
click on Morgan's Library
click on Constraints
search for deferred and deferrable

The basic point being that I can defer validation against the constraint. So normally one would have to do this:

INSERT INTO parent
INSERT INTO child
COMMIT; With a deferred constraint I could do it that way or:

INSERT INTO child
INSERT INTO parent
COMMIT; and it would be equally valid. Run the demos and also check out the concept docs at tahiti.oracle.com.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu Jan 27 2005 - 10:50:39 CST