Hello folks Checkout this scenario Table 1 columns -> | table_1_id (pkey) | some_column | comments | Table 2 columns -> | table_2_id (pkey) | some_other_column | table_1_id (fkey) | comments | All primary keys are of type serial or auto number. The 3rd column on Table 2 is an fk that references Table 1's primary key. I would like to insert rows into both programmaticaly (from a c++ app) Do i have to insert to table one then SELECT-query the entry's primary key then insert the Table 2 row with the pkey result? Is there a more efficient way of handling this? Say using almost 2 queries? PS. A postgresql specific solution would also work
1. Insert into table 1 2. get the last_insert_id 3. insert into table 2 On Wed, Jan 19, 2011 at 1:57 PM, James Nzomo <kazikubwa@gmail.com> wrote:
Hello folks Checkout this scenario
Table 1 columns -> | table_1_id (pkey) | some_column | comments |
Table 2 columns -> | table_2_id (pkey) | some_other_column | table_1_id (fkey) | comments |
All primary keys are of type serial or auto number. The 3rd column on Table 2 is an fk that references Table 1's primary key.
I would like to insert rows into both programmaticaly (from a c++ app)
Do i have to insert to table one then SELECT-query the entry's primary key then insert the Table 2 row with the pkey result?
Is there a more efficient way of handling this? Say using almost 2 queries?
PS. A postgresql specific solution would also work
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
That's what i usually do but i want to know if there is a more efficient way to set the foreign key on insert
Hi James, In MySQL you could use "select last_insert_id()" to get the insert id of table 1 and use that as the fk in table 2. In Postgresql I think it is a bit more complicated. Something like: SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name')); On Wed, Jan 19, 2011 at 2:11 PM, James Nzomo <kazikubwa@gmail.com> wrote:
That's what i usually do but i want to know if there is a more efficient way to set the foreign key on insert
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards Brian Ngure
That would work for a single user scenario... But what happens when multiple users are doing the same thing almost simultaneously??? The wrong foreign key values will be inserted to Table 2 with disastrous effects
Consider this happening in the 1st second of 10:15 am @10:15:01:025 User 1 inserts to Table 1 @10:15:01:050 User 1 Selects last inserted pkey from Table 1 @10:15:01:075 User 1 inserts to Table 2 All good -------------------------------------------------------------------------------------------------------------------- Now consider this alternate scenario happening in the same 1st second of 10:15 am @10:15:01:025 User 1 inserts to Table 1 @10:15:01:030 User 2 inserts to Table 1 @10:15:01:050 User 1 Selects last inserted pkey from Table 1 @10:15:01:075 User 1 inserts to Table 2 Chaos! All in less than a second
You would need to lock the tables between writes. May slow down the system somewhat. On Wed, Jan 19, 2011 at 2:49 PM, James Nzomo <kazikubwa@gmail.com> wrote:
Consider this happening in the 1st second of 10:15 am
@10:15:01:025 User 1 inserts to Table 1 @10:15:01:050 User 1 Selects last inserted pkey from Table 1 @10:15:01:075 User 1 inserts to Table 2
All good
-------------------------------------------------------------------------------------------------------------------- Now consider this alternate scenario happening in the same 1st second of 10:15 am
@10:15:01:025 User 1 inserts to Table 1 @10:15:01:030 User 2 inserts to Table 1 @10:15:01:050 User 1 Selects last inserted pkey from Table 1 @10:15:01:075 User 1 inserts to Table 2
Chaos! All in less than a second
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards Brian Ngure
Locking may be a good option but the performance to integrity tradeoff ..... Locking might also frustrate users when trying to insert Anyway, I think i'll use a different way of getting the primary key other than relying on the last insert Or maybe employ the use of a surrogate key I'll research on the matter then share the best solution in my view
Well, I'm a Java nerd. In java theres a way you can specify that you want the generated keys returned to you upon insertion into a table with auto generated keys. Since JDBC works with virtually any database, I think that should tell us that there is a way to get the results by maybe using a handle returned from the insert statement. Check out the docs for whatever drivers you are using to connect to the db and you'll most probably find it there. On Wed, Jan 19, 2011 at 3:05 PM, James Nzomo <kazikubwa@gmail.com> wrote:
Locking may be a good option but the performance to integrity tradeoff ..... Locking might also frustrate users when trying to insert
Anyway, I think i'll use a different way of getting the primary key other than relying on the last insert Or maybe employ the use of a surrogate key
I'll research on the matter then share the best solution in my view
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Solomon Kariri, Software Developer, Cell: +254736 729 450 Skype: solomonkariri
you get the last insert id based on your connection, and concurrent operations will have different connections, so the case of mixed up keys wont happen. A more real concern though would be if your insert fails, this would mean that getting the last id would fetch the last successful insert on that connection and use it On Wed, Jan 19, 2011 at 3:05 PM, James Nzomo <kazikubwa@gmail.com> wrote:
Locking may be a good option but the performance to integrity tradeoff ..... Locking might also frustrate users when trying to insert
Anyway, I think i'll use a different way of getting the primary key other than relying on the last insert Or maybe employ the use of a surrogate key
I'll research on the matter then share the best solution in my view
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On Wed, Jan 19, 2011 at 3:19 PM, Chris Mwirigi <mwirigic@gmail.com> wrote:
you get the last insert id based on your connection, and concurrent operations will have different connections, so the case of mixed up keys wont happen. A more real concern though would be if your insert fails, this would mean that getting the last id would fetch the last successful insert on that connection and use it
The behaviour of last_insert_id() changes based on the version of MySQL you are using, especially if the most recent INSERT or UPDATE failed. The page below explains the differences: http://dev.mysql.com/doc/refman/5.1/en/mysql-insert-id.html
Hello James, Using stored procedures might be your best shot at getting the most accurate results. You can possibly combine this with the use of database transactions. 1) The procedure for Inserting to the first table should have the id / key of the inserted record as its return value to the C++ program. E.g; -- This is more of a MySQL query. I have omitted the specific syntax of creating stored procedures ( DELIMITER // .... CREATE OR REPLACE PROCEDURE ....... AS ...... BEGIN ); INSERT INTO table_one values ( x, y, z); RETURN last_insert_id ; 2) The calling program should use the value returned, say an integer and use it to do the next Insert, maybe by calling another procedure and using the value returned as a parameter. Martin.
do not use a serial but a sequence - that way you have a unique value (just read up on sequences)
In MySQL you could use "select last_insert_id()" to get the insert id of table 1 and use that as the fk in table 2.
eek! what happens if users insert at the same time? On Wed, Jan 19, 2011 at 3:05 PM, James Nzomo <kazikubwa@gmail.com> wrote:
Locking may be a good option but the performance to integrity tradeoff ..... Locking might also frustrate users when trying to insert
Anyway, I think i'll use a different way of getting the primary key other than relying on the last insert Or maybe employ the use of a surrogate key
I'll research on the matter then share the best solution in my view
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Best Regards,
On Wed, Jan 19, 2011 at 2:42 PM, James Nzomo <kazikubwa@gmail.com> wrote:
That would work for a single user scenario... But what happens when multiple users are doing the same thing almost simultaneously???
James, last_insert_id() uses the same connection used by the most recent INSERT, and since each user has a unique connection identifier, there is no way where the fkey returned by last_insert_id() will be the same for two different users.
Therefore, there will be no need to lock the tables. http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html *For LAST_INSERT_ID()<http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id>, the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID()<http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id>and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed. *
@Peter I thought there was something like that. Thanks for the clarification :) On Wed, Jan 19, 2011 at 3:19 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
On Wed, Jan 19, 2011 at 2:42 PM, James Nzomo <kazikubwa@gmail.com> wrote:
That would work for a single user scenario... But what happens when multiple users are doing the same thing almost simultaneously???
James, last_insert_id() uses the same connection used by the most recent INSERT, and since each user has a unique connection identifier, there is no way where the fkey returned by last_insert_id() will be the same for two different users.
Therefore, there will be no need to lock the tables.
http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html
*For LAST_INSERT_ID()<http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id>, the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID()<http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id>and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed. *
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards Brian Ngure
I pondered on Simon Kariri's post and remembered postgre has similar functionality. Then i checked out the postgre documentation I found a simple solution to sort it out in 2 queries! Haven't checked for MySQL since i'm using PgSQL 1 Insert to table 1 and get the primary key in ONE QUERY *PGresult* result = PGquery(*"INSERT INTO TABLE1 (table_1_id,some_column) VALUES (DEFAULT, 'value flani')* RETURNING table_1_id**");* 2 Insert to table 2 using the returned result from the previous query** *PGquery(*"INSERT INTO TABLE2 (table_2_id,some_other_column, table_1_id) VALUES (DEFAULT, 'value flani',"*+ PQgetvalue(result, 0, 0) +*")"*);* Fast and Simple! No programming overhead
@christian The serial type is a sequence with default parameters. Both are enforce unique values
participants (7)
-
Brian Ngure -
Chris Mwirigi -
Christian Ledermann -
James Nzomo -
Martin Chiteri -
Peter Karunyu -
solomon kariri