Thread: Unique Indexes
Hi, I'm new to Postgres and plpgsql so my sincerest apologies if the answer to my problem is common :-) I want to add entries to a table. The table has 2 fields, FieldID which is the primary key and FieldValue which is a unique field. I can add to FieldValue just fine when the unique constraint hasn't been breached. However, if I try to add to FieldValue and there's already another field with that value, obviously I'll get an error. My problem is that I want to: a) Be able to detect when an error occurs and be able to read that error b) Find out the FieldID for the row which already has the value in it. So if I try to enter 'blah' into FieldValue and it's already in the row with FieldID=3, I want the value 3 to be returned to me. I realise this can be done using a SELECT after the error, but I'd like to avoid that. I'm hoping Postgres itself has some way of detecting this sort of thing. Even though I'm new to Postgres, I have looked at triggers and functions and I'm feeling fairly confident with them, so please, if that's what it takes, suggest it :-) Thanks! _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
On Fri, Mar 11, 2005 at 04:21:49PM +0000, ILove TheSpam wrote: > My problem is that I want to: > a) Be able to detect when an error occurs and be able to read that error > b) Find out the FieldID for the row which already has the value in it. So > if I try to enter 'blah' into FieldValue and it's already in the row with > FieldID=3, I want the value 3 to be returned to me. I realise this can be > done using a SELECT after the error, but I'd like to avoid that. I'm hoping > Postgres itself has some way of detecting this sort of thing. 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...." -- Michael Fuhr http://www.fuhr.org/~mfuhr/
>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/
On Sat, Mar 12, 2005 at 10:41:08 +0000, ILove TheSpam <ilovethespam@hotmail.com> wrote: > 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 This is normally a bad design. Why are you doing this? If these are real people's names, you don't want enforce that there aren't two people with the same name unless you have some way to keep such combinations of people out of your problem (perhaps by making them change their names?). There doesn't seem to be any point to the firstname and surnames tables. If you want this for searching, nonunique indexes on the firstnameid and surnameid fields of the users table should work. Normally you would do this with a single user table that has an artificial primary key to identify unique people, and then information about their name and maybe some other properties of people. Checking for duplicates may be hard, because it can be hard in real like to tell if two people with the same same are really two people.