Re: Unique Index on a view

There are a few ways I can think of to do this but none particularly efficient.

The simplest is to create a control table with a matching primary key. As a pre-insert trigger insert a matching key into the control table. (And delete it with a pre-delete trigger, and generally fiddle about with an update trigger).

As a variant, with a lot more code, but not leaving a filled control table use a pre-insert like:

    select row for update from other table     if row exists, then raise error
    if row does not appear to exist then

        insert row into control tabl
    This may hang if another user is currently     inserting the row in to the other table, and     fail/succeed when the other users rollsback/commits then on a post-insert, you delete the control row.

(this is a bit off the cuff, but it looks as if it would work - I won't guarantee that there won't be a possible race or deadlock condition).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Brian Tkatch wrote in message <3a1bbd11.129376281_at_news.alt.net>...


>On Wed, 22 Nov 2000 02:54:30 GMT, David Grzebien

><dgrzebie_at_columbus.rr.com> wrote:

>

>The point is not to have a contraint on a view, or even to limit its

>output. I was trying to accomplish uniqueness in one row for two

>tables. Just curious if it is in someway possible.

>

>Brian