Re: Unique Indexes

>Could you describe the problem you're trying to solve?  It might
>be easier to help if we knew the ultimate purpose of what you're
>trying to do.  Something like "I want to do this because...."

Sure.

Lets say I have 3 tables:

surnames - surnameid serial (Primary Key), surname varchar (Unique)
firstnames - firstnameid serial (Primary Key), firstname varchar (Unique)
users - userid serial (Primary Key), firstnameid, surnameid

I enter the following firstname, surname combinations....

joe, smith
john, doe
jason, jones
john, smith

In the first case, the first and last names are entered into the tables with
no problems since the tables are empty. After entering, I need to get the
"firstnameid" and "surnameid" of the successful insertions (let's assume I
can do this with no problems using some magical code I've written). Once I
have them, I create a new row in the "users" table with these ids.

We then move to the second case where the same thing happens. Everything is
satisfied so we grab the "firstnameid" and "surnameid" from the inserts of
john and doe and create a new row in the "users" table.

The same situation applies to the third case.

The problem comes up in the fourth case. We can insert the firstname fine,
but when we try to insert "smith" into the "surnames" table, it fails
because we've already entered "smith".

What I'd like to do is find out the surnameid of the "smith" already in the
"surnames" table. With that, I can create a new row in the "users" table
with the firstnameid we have and the surnameid of the "smith" already in the
"surnames" table.

As I said before, I know that after I find out the row didn't insert due to
the unique constraint I can use a SELECT to get the surnameid, but I'd like
to avoid this 'overhead'. I'd much prefer if Postgres itself had some way to
do this using something like triggers.

I hope that explains everything properly.

Thanks!

_________________________________________________________________
Don�t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/