From: jasondma@my-deja.com
Newsgroups: comp.databases.theory
Subject: Theory ques: To autogenerate PK or not???????
Date: Thu, 12 Oct 2000 15:35:29 GMT
Organization: Deja.com - Before you buy.
Lines: 73
Message-ID: <8s4lo0$iuk$1@nnrp1.deja.com>
NNTP-Posting-Host: 216.203.181.210
X-Article-Creation-Date: Thu Oct 12 15:35:29 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
X-Http-Proxy: 1.1 x66.deja.com:80 (Squid/1.1.22) for client 216.203.181.210
X-MyDeja-Info: XMYDJUIDjasondma


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.