what if i need the primary key of a row that is to be inserted?

[hardc0d3r]

what if i need the primary key of a row that is to be inserted? that primary key will be used to insert another row from another table.. what i was doing was after inserting the row, i get the primary key by select statement and use that value to insert a row from another table.. is this ok? are there any more efficient ways of doing this?

[Martijn Tonies]

>
> what if i need the primary key of a row that is to be inserted? that
primary
> key will be used to insert another row from another table.. what i was
doing
> was after inserting the row, i get the primary key by select statement and
> use that value to insert a row from another table.. is this ok? are there
> any more efficient ways of doing this?

I take it you mean a "primary key value that comes from an auto-increment
column"?

Next, we have to assume you're doing this:

insert into mytable ( ... ) values ( ... )

select max(ID-column) from mytable

re-use the value to insert child records?

Is that correct?

If so, I'd say this will ONLY work properly in a multi-user system if
you're using transactions and you don't commit between the actual
INSERT and SELECT.


It's a pity that MySQL doesn't understand the INSERT INTO ... RETURNING
syntax, as this would solve your problem :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[hardc0d3r]

yup, that's it.. thanks for the reply..

[Martijn Tonies]

> >> what if i need the primary key of a row that is to be inserted? that
> > primary
> >> key will be used to insert another row from another table.. what i
> >> was
> > doing
> >> was after inserting the row, i get the primary key by select
> >> statement and use that value to insert a row from another table.. is
> >> this ok? are there any more efficient ways of doing this?
> >
> > I take it you mean a "primary key value that comes from an
> > auto-increment column"?
> >
> > Next, we have to assume you're doing this:
> >
> > insert into mytable ( ... ) values ( ... )
> >
> > select max(ID-column) from mytable
> >
> > re-use the value to insert child records?
> >
> > Is that correct?
> >
> > If so, I'd say this will ONLY work properly in a multi-user system if
> > you're using transactions and you don't commit between the actual
> > INSERT and SELECT.
> >
> >
> > It's a pity that MySQL doesn't understand the INSERT INTO ...
> > RETURNING syntax, as this would solve your problem :-)
> >
>
> i agree that if he's trying to select the highest value in some manner
> as you describe he's in trouble, but there is of course
> "last_insert_id()", which does solve his problem.
>  <http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#funct
> ion_last-insert-id>

Ah yes, I was searching for that in the docs but couldn't remember it :-)

Thanks!

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Michael Dykman-2]

On 8/24/07, hardc0d3r <hardc0d3r@...> wrote:
>
> yup, that's it.. thanks for the reply..
> --


More typically, you would use the last_insert_id() function which will
return the value of the most recently created auto-increment key...

CREATE TABLE mytable (
id int auto_increment primary key,
foo char(1)
);

 INSERT INTO mytable (foo) VALUES('a');
SELECT LAST_INSERT() AS newkey;

calculating the max key + 1 , as it has been noted, must be done
transactionally and will therefore require a transactional table
type...  The catch is that, unlike MyISAM, Innodb does not maintain
column meta information like min, max, sum so this approach will get
more expensive with each subsequent call.
--
 - michael dykman
 - mdykman@...

 - All models are wrong.  Some models are useful.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Rudy Lippan]

On Fri, 24 Aug 2007, Michael Dykman wrote:

> calculating the max key + 1 , as it has been noted, must be done
> transactionally and will therefore require a transactional table

And with a transaction isolation level greater than Read Commited...

-r

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...