Theory ques: To autogenerate PK or not???????

From: <jasondma_at_my-deja.com>
Date: Thu, 12 Oct 2000 15:35:29 GMT
Message-ID: <8s4lo0$iuk$1_at_nnrp1.deja.com>

Hi everyone,

I have a DB theory dilemma that I or my colleagues can't resolve.

I have been pondering the question of whether or not to autogenerate Primary keys or manage them using a key table for some time and I have not been able to come to a satisfactory conclusion. The problem as I see it comes down to this:

Hypothetically, say there are 3 tables for customers, products and a join linking customers and products

customer

---

cust_id (some integer datatype)
first_name (some char string datatype)
...

products

---

product_id (the same integer datatype)
product_name some char string datatype)

customer_products

---

(insert PK cust_prod_id at your discretion) cust_id
prod_id
...

The issue arises when trying to insert records into the customer table and the customer_products table in 1 transaction.

with autoincrement field:
insert into customers values(null, first_name,...)

/*I don't know the key assigned to the new row so I must retrieve it*/
var = select cust_id from customers where first_name = first_name

insert into customer_products values (var, prod_id,...)

another solution is to handle the key generation manually in a key table of some sort. then the insert looks like this:

key_from table = select key from keytable where table_name = customers
/*a stored proc would be better so as to increment and return the value
/*but that is a little out of scope*/

insert into customer values(key_from_table, first_name,...) insert into customer_product(key_from_table, prod_id,...)

What I really would hope for is the that DBMS gives access to the generated key and returns it on insert but they don't(Oracle returns ROWNUM, PostgreSQL returns oid, I am not sure of the others) I am left to either writing functions or procedures to kick back the PK of the table or selecting and updating out of a keytable. Neither of these seems clean to me.

I am curretnly looking for a postgreSQL solution but I have experience with MSSQL6.5-7.0, Oracle 8i, PostgreSQL, Sybase ASA, ASE.

any help or religious beliefs would be most appreciated

Thanks in advance,

Jason

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 12 2000 - 17:35:29 CEST