MySQL: Multiple Primary Keys and Auto Increment

mysql auto-increment composite-primary-key

AdmiralJonB · Jun 19, 2012 · Viewed 9.4k times · Source

I'm quite new to setting up tables in MySQL and there is something I'd like to do which is a bit more advance than I'm able to do.

I have two columns as part of a composite primary key, one is a Date and an ID I would like to be an auto increment integer. For each date, I would like to reset the auto integer to 0, so something like this:

|-----------------|
|Date       | ID  |
|-----------------|
|2012-06-18 | 1   |
|2012-06-18 | 2   |
|2012-06-18 | 3   |
|2012-06-19 | 1   |
|2012-06-19 | 2   |
|2012-06-20 | 1   |
|-----------------|

Thanks

Answer

Here this should work.

CREATE TABLE  `answer`(
  `dates` DATE NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`dates`,`id`)
) ENGINE=MyISAM;

It is known to cause problems with innoDB. Hope this helps you.

EDIT: RESULTS

2012-06-19  1
2012-06-19  2
2012-06-19  3
2012-07-19  1
2012-07-19  2
2012-08-19  1

On php myadmin.

MySQL: Multiple Primary Keys and Auto Increment

Answer

Related questions