Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Multiple Primary Keys and Auto Increment

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

like image 764
AdmiralJonB Avatar asked Jun 19 '12 11:06

AdmiralJonB


People also ask

Can we have 2 auto increment in MySQL?

MySQL server already provides two auto increment variables: auto_increment_increment and auto_increment_offset, which can be used to generate different auto increment values on each member.

Can you have multiple primary keys MySQL?

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Can auto increment be a primary key?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Is Serial the same as auto increment?

In MySQL, both SERIAL and AUTO_INCREMENT are used to define a sequence as a default value for a field. But they are technically different from each other. The AUTO_INCREMENT attribute is supported by all numeric data types except for BIT and DECIMAL.


2 Answers

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.

like image 198
ppsreejith Avatar answered Sep 23 '22 20:09

ppsreejith


Well, for me mysql does what you want automatically.

mysql> CREATE TABLE TestData(Date date not null, ID int unsigned not null auto_increment, PRIMARY KEY(Date, ID));

mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-19";
mysql> INSERT INTO TestData SET Date = "2012-06-19";
mysql> INSERT INTO TestData SET Date = "2012-06-20";

mysql> select * from TestData;
+------------+----+
| 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 |
+------------+----+

No magic involved.

like image 23
Johan Soderberg Avatar answered Sep 24 '22 20:09

Johan Soderberg