db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: Auto increment fields , evil or good ? I need advice on a design
Date Tue, 22 Nov 2005 21:59:04 GMT
On Tuesday 22 November 2005 14:39, Legolas Woodland wrote:
> Hi
> Thank you for reading my post.
> here is my question :
> using auto increment fields are good or bad ?
> what is best practice for them : When to use them , how to use them , when
> to avoide them?
>
Auto increment is not evil.
Burt is evil!  [http://www.funkatron.com/bert/bert.htm] {Its an old net gag}

> consider the following condition :
> I have one table which is related to another table in a one-to-one
> relation. parent table primary key is auto increment (what should i use if
> auto increment is evil?)
> now i insert a record into parent Table , i should include parent table
> Primary key into child table
> to relate it with parent table.
> after i insert parent record i should execute a select statement to find
> the inserted record Primary key and include it into child table record My
> question is :
> how i can avoide this select statement execution ?
> what should i use instead of That auto increment field ?
>
Well, this is a good example of why to use an auto increment feature of any 
database. The hard part occurs when you're expecting that all rows are going 
to be in numerical sequence with no gaps. (1,2,4,6) for example can occur 
within an identity column.

So if you were doing a master/detail app, like an invoice with header and then 
row count detail, you would have some issues. There an identity column 
doesn't make sense, unless of course you're using it for an order number 
identity. (And even then there's a couple of strategies that would better 
serve you...)

How to get the inserted value after the insert of the primary key and prior to 
the insert of the child record?
Thats a tough one. You have to execute a statement to use the 
IDENTITY_VAL_LOCAL() to get that id number.

Look at page 85-86 for some examples. 
Note that a values IDENTITY_VAL_LOCAL() performed right after the insert will 
get you the value, however you're still executing an SQL statement.

Now I haven't looked at the resultSet method, but if you use it to insert a 
row vs executing an INSERT statement, you may be able to pull that data from 
it.

HTH,

-G


>
> Thank you so much.

Mime
View raw message