Absolutely nothing prevents you from assigning any arbitrary value to an AUTO_INCREMENT
column. If necessary, the table counter will adjust accordingly.
However, you cannot set as AUTO_INCREMENT
a column that's not unique.
Honestly, I can't understand your design. A typical messaging system would look like this:
message_id in_reply_to
========== ===========
1 NULL
2 NULL
3 1
4 NULL
5 1
6 3
7 NULL
Duplicating IDs kind of beats the purpose of using IDs.
Update #1: OMG, it seems that it can actually be done under certain circumstances:
For MyISAM tables you can specify AUTO_INCREMENT on a secondary column
in a multiple-column index. In this case, the generated value for the
AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1
WHERE prefix=given-prefix. This is useful when you want to put data
into ordered groups.
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
Update #2: For the records, I've just tested it and you can use duplicate auto-incremented IDs in InnoDB tables as well:
CREATE TABLE foo (
foo_id INT(10) NOT NULL DEFAULT '0',
bar_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (foo_id),
INDEX bar_id (bar_id)
)
ENGINE=InnoDB