Multiple auto increments in one column?

[Nick]

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

[Aggro]

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'.

[Aggro]

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'.

[Aggro]

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'.

[Nick]

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 |
+---+---+

[Nick]

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 |
+---+---+

[Nick]

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 |
+---+---+

[Nick]

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

[Nick]

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

[Nick]

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

[Aggro]

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.

[Aggro]

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.

[Aggro]

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.