List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:March 20 2009 4:34am
Subject:Auto increment without uniquness
View as plain text  
I think ideally I would like to create an auto increment column that  
has no requirement for uniqueness.  So if 6 was the last entry, and  
there are 10 of them, 7 would still be the next, is this possible?

I am assuming it is not?

I am working in a case where data is needing to be de-normalized, and  
I have never had to do this before.  In the past, this would be a join  

Now I have one table, it will have a single parent record, with x  
children records.  I start with an `id` primary key auto inc field,  
standard stuff.

Keeping it simple, lets say I have:
id, group_id, foo, bar, baz

I will do a batch of inserts, where the first record is the parent,  
and the rest are children.  The first parent record is what is getting  
me stuck, I need to give it a group_id, but all the children will need  
to have the same group id.

I could do one insert on the parent, get the returned id, and then  
update the group_id on the parent to the same id, but that is an  
insert and an update, I want to avoid the update.

Can I insert into table set foo = 'test', `group_id` = `id`
Or is that too soon in the insert trasaction to be noticed?

I can allow the parent to have an empty group_id, and use the id as  
what I search on, but it seems messy.

Thank you all
Scott * If you contact me off list replace talklists@ with scott@ *

Auto increment without uniqunessScott Haneda20 Mar