[thelist] SQL: insert into mutiple tables help

jeff jeff at members.evolt.org
Tue Apr 10 18:43:57 CDT 2001


rudy & cory,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: rudy
:
: first of all, without getting into the
: semantics of primary keys, suffice to
: say that most people use identity fields
: (sql/server), sequences (oracle), autonumbers
: (access), or auto_increments (mysql)
:
: which leads some people to doing the insert
: followed by
:
:    select max(id) from thetable
:
: but this can give back the wrong value under
: circumstances of concurrent users
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

easily solved, depending on your database and middleware.  for a database
that supports transactions, it's as simple as performing the insert and the
select max(id) within the same transaction block -- eliminating the chance
that another, nearly simultaneous request could grab the newly created id
from the insert.

in coldfusion you'd do it like this:

<cftransaction>
  <cfquery ...>
    INSERT ...
  </cfquery>
  <cfquery ...>
    SELECT Max(id)
  </cfquery>
</cftransaction>

this is much more reliable than querying for some piece of text you just
inserted and grabbing the id from the resulting recordset (see last
paragraph for an example).

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: to discourage yourself from ever being
: tempted to use select max(id) on an
: autonumber, define it random
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

as a dba, to encourage homicidal tendencies in developers that don't have
access to change the data schema, define default column values as random.
*grin*

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: note that you should probably have a unique
: constraint on the email column anyway
: (otherwise, it's possible to insert two
: subscribers with the same email id, and
: how nice would that be?)
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

a constraint is only half the solution to the problem of unique data in a
particular column.  the constraint itself will cause your middleware to
throw an error if you insert duplicate data.  however, you must also build
constraint-type checks into your middleware logic to prevent this error from
occurring at all.  at this point, the only reason for a constraint on that
column in the database is to prevent people with direct access to the
database from entering duplicate data.

another thing to look out for with using constraints as a pk of some sort is
that some databases treat the case of a string differently.  so, a user that
enters JoeUser at email.com would be different than joeuser at email.com, even
though we all know that's the same thing.

hope this helps,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org





More information about the thelist mailing list