469,344 Members | 6,115 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Multiple auto increments in one column?

Is it possible to have multiple auto increments in one column? Say I
have two tables...

1. table 'messageboards' with fields ('id' , 'name')

2. table 'messagethreads' with fields ('id' , 'messagethread' ,
'messageboard_id')

The 'messagethreads.id' column could have multiple auto increments.
Then I could easily query a messageboard with ("SELECT * FROM
messagethreads WHERE messageboard_id = 1 ORDER BY id"). Each thread
would return an id starting from number 1. If MySQL does not support
multiple auto increments, I guess the only other solutions are...

- Create a new table for every 'messageboard' with an auto increment
column and join them to 'messagethreads' for a query. Id like to use
as few tables as possible though.

- Use transactions or table locking to generate the auto increment
value. Id like to not use either for performance reasons.

Anyone have a better solution? Im sure its been done? Thanks!

-Nick
Jul 19 '05 #1
12 5278
Nick wrote:
Is it possible to have multiple auto increments in one column? Say I
have two tables...

1. table 'messageboards' with fields ('id' , 'name')

2. table 'messagethreads' with fields ('id' , 'messagethread' ,
'messageboard_id')

The 'messagethreads.id' column could have multiple auto increments.
Then I could easily query a messageboard with ("SELECT * FROM
messagethreads WHERE messageboard_id = 1 ORDER BY id"). Each thread
would return an id starting from number 1. If MySQL does not support
multiple auto increments, I guess the only other solutions are...


Why would they need to start from number 1? What difference does it make
if they start from 543 or 43645? They will still be in correct order and
you will only get messagethreads for the correct messageboard, since you
use 'where messageboard_id = 1'.
Jul 19 '05 #2
Nick wrote:
Is it possible to have multiple auto increments in one column? Say I
have two tables...

1. table 'messageboards' with fields ('id' , 'name')

2. table 'messagethreads' with fields ('id' , 'messagethread' ,
'messageboard_id')

The 'messagethreads.id' column could have multiple auto increments.
Then I could easily query a messageboard with ("SELECT * FROM
messagethreads WHERE messageboard_id = 1 ORDER BY id"). Each thread
would return an id starting from number 1. If MySQL does not support
multiple auto increments, I guess the only other solutions are...


Why would they need to start from number 1? What difference does it make
if they start from 543 or 43645? They will still be in correct order and
you will only get messagethreads for the correct messageboard, since you
use 'where messageboard_id = 1'.
Jul 19 '05 #3
Nick wrote:
Is it possible to have multiple auto increments in one column? Say I
have two tables...

1. table 'messageboards' with fields ('id' , 'name')

2. table 'messagethreads' with fields ('id' , 'messagethread' ,
'messageboard_id')

The 'messagethreads.id' column could have multiple auto increments.
Then I could easily query a messageboard with ("SELECT * FROM
messagethreads WHERE messageboard_id = 1 ORDER BY id"). Each thread
would return an id starting from number 1. If MySQL does not support
multiple auto increments, I guess the only other solutions are...


Why would they need to start from number 1? What difference does it make
if they start from 543 or 43645? They will still be in correct order and
you will only get messagethreads for the correct messageboard, since you
use 'where messageboard_id = 1'.
Jul 19 '05 #4
Well (8 hours later) it turns out you can have multiple auto
increments in one column. Heres how...

Create a table with two or more columns. One column will be the auto
increment and the other column will designate which auto increment to
join. These two columns must be primary keys. Another caveat is that
you MUST create the auto increment column AFTER the designater column.
Heres an example...

--> create table test(a int not null, b int auto_increment not null,
primary key(a,b));

--> insert into test values(1);
--> insert into test values(1);
--> insert into test values(1);
--> insert into test values(2);
--> insert into test values(2);
--> insert into test values(2);
--> insert into test values(3);
--> insert into test values(3);
--> insert into test values(3);

--> select * from test;

+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+---+---+
Jul 19 '05 #5
Well (8 hours later) it turns out you can have multiple auto
increments in one column. Heres how...

Create a table with two or more columns. One column will be the auto
increment and the other column will designate which auto increment to
join. These two columns must be primary keys. Another caveat is that
you MUST create the auto increment column AFTER the designater column.
Heres an example...

--> create table test(a int not null, b int auto_increment not null,
primary key(a,b));

--> insert into test values(1);
--> insert into test values(1);
--> insert into test values(1);
--> insert into test values(2);
--> insert into test values(2);
--> insert into test values(2);
--> insert into test values(3);
--> insert into test values(3);
--> insert into test values(3);

--> select * from test;

+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+---+---+
Jul 19 '05 #6
Well (8 hours later) it turns out you can have multiple auto
increments in one column. Heres how...

Create a table with two or more columns. One column will be the auto
increment and the other column will designate which auto increment to
join. These two columns must be primary keys. Another caveat is that
you MUST create the auto increment column AFTER the designater column.
Heres an example...

--> create table test(a int not null, b int auto_increment not null,
primary key(a,b));

--> insert into test values(1);
--> insert into test values(1);
--> insert into test values(1);
--> insert into test values(2);
--> insert into test values(2);
--> insert into test values(2);
--> insert into test values(3);
--> insert into test values(3);
--> insert into test values(3);

--> select * from test;

+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+---+---+
Jul 19 '05 #7
Thanks for responding, I actually needed them to start from 1 so that
when someone views the first message of a message board, the message
starts at 1. It might be a little weird to start a new message board
with a thread starting from 15,234. The solution I posted seems to be
what I needed. What do you think? -Nick
Jul 19 '05 #8
Thanks for responding, I actually needed them to start from 1 so that
when someone views the first message of a message board, the message
starts at 1. It might be a little weird to start a new message board
with a thread starting from 15,234. The solution I posted seems to be
what I needed. What do you think? -Nick
Jul 19 '05 #9
Thanks for responding, I actually needed them to start from 1 so that
when someone views the first message of a message board, the message
starts at 1. It might be a little weird to start a new message board
with a thread starting from 15,234. The solution I posted seems to be
what I needed. What do you think? -Nick
Jul 19 '05 #10
Nick wrote:
Thanks for responding, I actually needed them to start from 1 so that
when someone views the first message of a message board, the message
starts at 1. It might be a little weird to start a new message board
with a thread starting from 15,234. The solution I posted seems to be
what I needed. What do you think? -Nick


What ever works for you ;)

But if you print the numbers using a programming language, you don't
have to print the number you have in your variable. You can always take
something of it, or even use complitely different numbers.

So instead of printing the id value, you could just do something like:
for( i = 1; i < amount_of_messages; i++ )
{
print "messagenumber: " + i + "\n";
}

But yes, your solution it good also, perhaps even better if it doesn't
cause too much delay.
Jul 19 '05 #11
Nick wrote:
Thanks for responding, I actually needed them to start from 1 so that
when someone views the first message of a message board, the message
starts at 1. It might be a little weird to start a new message board
with a thread starting from 15,234. The solution I posted seems to be
what I needed. What do you think? -Nick


What ever works for you ;)

But if you print the numbers using a programming language, you don't
have to print the number you have in your variable. You can always take
something of it, or even use complitely different numbers.

So instead of printing the id value, you could just do something like:
for( i = 1; i < amount_of_messages; i++ )
{
print "messagenumber: " + i + "\n";
}

But yes, your solution it good also, perhaps even better if it doesn't
cause too much delay.
Jul 19 '05 #12
Nick wrote:
Thanks for responding, I actually needed them to start from 1 so that
when someone views the first message of a message board, the message
starts at 1. It might be a little weird to start a new message board
with a thread starting from 15,234. The solution I posted seems to be
what I needed. What do you think? -Nick


What ever works for you ;)

But if you print the numbers using a programming language, you don't
have to print the number you have in your variable. You can always take
something of it, or even use complitely different numbers.

So instead of printing the id value, you could just do something like:
for( i = 1; i < amount_of_messages; i++ )
{
print "messagenumber: " + i + "\n";
}

But yes, your solution it good also, perhaps even better if it doesn't
cause too much delay.
Jul 19 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Olafur | last post: by
3 posts views Thread by Smitro | last post: by
2 posts views Thread by Mike N. | last post: by
3 posts views Thread by cmk128 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.