Search code examples
mysqlauto-increment

composite primary key and autoincrement column but NOT primary key

I'm trying to achieve this:

INSERT INTO `tbl_result` (`var1`, `var2`, `var3`, `year`, `result`) 
VALUES (%f, %f, %d, %d, %f)
ON DUPLICATE KEY UPDATE result=%f;

These are results of a realtime calculator that I want to uniquely store in the tbl_result database table, and maintain updated even if the calculation result changes. The above seems to me like the best way to do this.

However for this to work var1, var2, var3, year must be primary keys:

CREATE  TABLE `tbl_result` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `var1` DECIMAL(6,2) UNSIGNED NOT NULL ,
  `var2` DECIMAL(3,2) UNSIGNED NOT NULL ,
  `var3` INT(11) UNSIGNED NOT NULL ,
  `year` INT(4) UNSIGNED NOT NULL ,
  `result` DECIMAL(8,4) NOT NULL ,
  PRIMARY KEY (`var1`, `var2`, `var3`, `year`) 
);

But because I also need to store a unique id to cross reference results with multiple users. I get the following error:

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

MySql version is 5.5.31-0ubuntu0.12.04.1, is it possible to set the default of a column with a guid value?

Thanks, P.

P.S. this sounds like a plausible scenario to me, is it possible to report a suggestion to MySql?

Solution

  • One solution to this is using the ID as your primary key, and the var1, var2, var3, year fields as an alternate key by using an UNIQUE KEY constraint

    So, your table definition shall look like this:

    CREATE  TABLE `tbl_result` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `var1` DECIMAL(6,2) UNSIGNED NOT NULL ,
  `var2` DECIMAL(3,2) UNSIGNED NOT NULL ,
  `var3` INT(11) UNSIGNED NOT NULL ,
  `year` INT(4) UNSIGNED NOT NULL ,
  `result` DECIMAL(8,4) NOT NULL ,
  PRIMARY KEY (`ID`),
  UNIQUE KEY (`var1`, `var2`, `var3`, `year`) 
);

    The UNIQUE KEY constraint will prevent from duplicate insertions of your fields.