You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Legolas Woodland <le...@gmail.com> on 2005/11/22 21:39:36 UTC
Auto increment fields , evil or good ? I need advice on a design
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?
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 ?
Thank you so much.
Re: Auto increment fields , evil or good ? I need advice on a design
Posted by Michael Segel <ms...@segel.com>.
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.
Good vs Evil? Maybe I'm looking at this from a skewed perspective?
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 22 November 2005 16:19, Stanley Bradbury wrote:
>
> From JDBC you can use java.sql.Statement.getGeneratedKeys(). See this
> link for an example:
> http://publib.boulder.ibm.com/infocenter/cscv/v10r1/topic/com.ibm.cloudscap
>e.doc/crefjavstateautogen.html
>
> A description of IDENTITY_VAL_LOCAL function can be found at:
> http://publib.boulder.ibm.com/infocenter/cscv/v10r1/topic/com.ibm.cloudscap
>e.doc/rrefidentityvallocal.html#rrefidentityvallocal
Hmmm I had forgotten about the .getGeneratedKeys().
The reason I'm following up on this post is the URL that you point to in your
response.
Its a great resource, but something I saw caught my eye and I was thinking
about it while I was out walking the doggies....
From the IBM web site:
http://publib.boulder.ibm.com/infocenter/cscv/v10r1/index.jsp?topic=/com.ibm.cloudscape.doc/crefjavstateautogen.html
" IBM® Cloudscape™ is a relational database management system that is based on
Java™ and SQL. Cloudscape is a commercial release of the Apache Software
Foundation's (ASF) open source relational database project. The Apache
project is called Derby.
The Cloudscape product includes Derby without any modification whatsoever to
the underlying source code. Cloudscape includes the same core Derby engine
but provides a few features you won't find in the Derby software such as
installers (with a JRE) and translated manuals and error messages. In
addition, technical support is available for purchase for the Cloudscape
product through IBM.
"
This kind of confused me for a sec.
Is IBM continuing to release/resell Cloudscape?
Does it mean that Cloudscape will match Derby in fixes or will IBM make
corrections to Cloudscape that are not going to be made to Derby?
Just looking for some clarification.
--
Michael Segel
Principal
MSCC
312 952- 8175 [M]
Re: Auto increment fields , evil or good ? I need advice on a design
Posted by Stanley Bradbury <St...@gmail.com>.
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?
>
> 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 ?
>
>
> Thank you so much.
>
From JDBC you can use java.sql.Statement.getGeneratedKeys(). See this
link for an example:
http://publib.boulder.ibm.com/infocenter/cscv/v10r1/topic/com.ibm.cloudscape.doc/crefjavstateautogen.html
A description of IDENTITY_VAL_LOCAL function can be found at:
http://publib.boulder.ibm.com/infocenter/cscv/v10r1/topic/com.ibm.cloudscape.doc/rrefidentityvallocal.html#rrefidentityvallocal