Serial Fields
I have a problem with foreign keys using serial fields on a 1-to-many relationship. We have a USER table (Primary Key=user_id:serial), an ADDRESS table (Primary Key=address_id:serial) and an intermediate table USER_ADDRESS (Foreign Keys=user_id, address_id) to model the 1-to-many relationship. We are developing a web-based application that INSERTs a record on all 3 tables as 1 unit of work. I can't find a way to populate the USER_ADDRESS table properly. Can we get the next sequence number for USER and ADDRESS without actually doing a commit? How is this normally done in a client-server environment? Thanks in advance! * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
In article <15f5621a.bc88405c@usw-ex0109-069.remarq.com>, Anthony <kpsbuNOkpSPAM@hotmail.com.invalid> wrote: > I have a problem with foreign keys using serial fields on a > 1-to-many relationship. We have a USER table (Primary > Key=user_id:serial), an ADDRESS table (Primary > Key=address_id:serial) and an intermediate table > USER_ADDRESS (Foreign Keys=user_id, address_id) to model the > 1-to-many relationship. > What you're describing is a many to many. If it was 1 to many, you should have a serial field on USER, and an integer field on address, unless you have multiple users at one address. > We are developing a web-based application that INSERTs a > record on all 3 tables as 1 unit of work. I can't find a > way to populate the USER_ADDRESS table properly. Can we get > the next sequence number for USER and ADDRESS without > actually doing a commit? > sqlerrd[1] in the sqlca struct. You can get this after the successful insert of each of ther USER and ADDRESS records. There's a way to do it with straight SQL, but I can't remember it right now for the life of me. Something like 'select sqlca(sqlerrd1) from ...', but I know that's not right. Somebody want to help me out here? > How is this normally done in a client-server environment? > > Thanks in advance! > > * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
Anthony <kpsbuNOkpSPAM@hotmail.com.invalid> wrote: >I have a problem with foreign keys using serial fields on a >1-to-many relationship. We have a USER table (Primary >Key=user_id:serial), an ADDRESS table (Primary >Key=address_id:serial) and an intermediate table >USER_ADDRESS (Foreign Keys=user_id, address_id) to model the >1-to-many relationship. > >We are developing a web-based application that INSERTs a >record on all 3 tables as 1 unit of work. I can't find a >way to populate the USER_ADDRESS table properly. Can we get >the next sequence number for USER and ADDRESS without >actually doing a commit? > >How is this normally done in a client-server environment? > >Thanks in advance! > Isn't a serial field normally populated with an INSERT command by using a 0 as a place holder, and then the the serial field automatically updates the field with the correct sequential number? Wynne ----------------------------------------------------------- Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free! http://www.keen.com