Defining Composite Key with Auto Increment in MySQL

Question

Scenario:

I have a table which references two foreign keys, and for each unique combination of these foreign keys, has its own auto_increment column. I need to implement a Composite Key that will help identify the row as unique using combination of these three (one foreign keys and one auto_increment column, and one other column with non-unique values)

Table:

CREATE  TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
  `app_id` INT NOT NULL ,
  `test_id` INT NOT NULL ,
  `issue_name` VARCHAR(255) NOT NULL ,
primary key (app_id, test_id,sr_no)
);

Of course, there has to be something wrong with my query, because of which the error thrown is:

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

What I am trying to achieve:

I have an Application Table (with app_id as its primary key), each Application has a set of Issues to be resolved, and each Application has multiple number of tests (so the test_id col) The sr_no col should increment for unique app_id and test_id.

i.e. The data in table should look like:

The database engine is InnoDB. I want to achieve this with as much simplicity as possible (i.e. avoid triggers/procedures if possible - which was suggested for similar cases on other Questions).

Answer 1

You can't have MySQL do this for you automatically for InnoDB tables - you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key.

Something like the following should work

DELIMITER $$

CREATE TRIGGER xxx BEFORE INSERT ON issue_log
FOR EACH ROW BEGIN
    SET NEW.sr_no = (
       SELECT IFNULL(MAX(sr_no), 0) + 1
       FROM issue_log
       WHERE app_id  = NEW.app_id
         AND test_id = NEW.test_id
    );
END $$

DELIMITER ;

Answer 2

You can do this with myISAM and BDB engines. InnoDB does not support this. Quote from MySQL 5.0 Reference Manual.

For MyISAM and BDB 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.

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Answer 3

I don't fully understand your increment requirement on the test_id column, but if you want an ~autoincrement sequence that restarts on every unique combination of (app_id, test_id), you can do an INSERT ... SELECT FROM the same table, like so:

mysql> INSERT INTO `issue_log` (`sr_no`, `app_id`, `test_id`, `issue_name`) SELECT
           IFNULL(MAX(`sr_no`), 0) + 1 /* next sequence number */,
           3 /* desired app_id */,
           1 /* desired test_id */,
           'Name of new row'
           FROM `issue_log` /* specify the table name as well */
       WHERE `app_id` = 3 AND `test_id` = 1 /* same values as in inserted columns */

This assumes a table definition with no declared AUTO_INCREMENT column. You're essentially emulating autoincrement behavior with the IFNULL(MAX()) + 1 clause, but the manual emulation works on arbitrary columns, unlike the built-in autoincrement.

Note that the INSERT ... SELECT being a single query ensures atomicity of the operation. InnoDB will gap-lock the appropriate index, and many concurrent processes can execute this kind of query while still producing non-conflicting sequences.

Answer 4

You can use a unique composite key for sr_no,app_id & test_id. You cannot use incremental in sr_no as this is not unique.

CREATE TABLE IF NOT EXISTS `issue_log` (
  `sr_no` int(11) NOT NULL,
  `app_id` int(11) NOT NULL,
  `test_id` int(11) NOT NULL,
  `issue_name` varchar(255) NOT NULL,
  UNIQUE KEY `app_id` (`app_id`,`test_id`,`sr_no`)
) ENGINE=InnoDB ;

I have commented out unique constraint violation in sql fiddle to demonstrate (remove # in line 22 of schema and rebuild schema )

Answer 5

Just add key(sr_no) on auto-increment column:

CREATE  TABLE `issue_log` (
 `sr_no` INT NOT NULL AUTO_INCREMENT ,
 `app_id` INT NOT NULL ,
 `test_id` INT NOT NULL ,
 `issue_name` VARCHAR(255) NOT NULL ,
  primary key (app_id, test_id,sr_no),
  key (`sr_no`)
);

Answer 6

Why don't you try to change the position of declare fields as primary key, since when you use "auto_increment" it has to be referenced as the first. Like in the following example

CREATE  TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
  `app_id` INT NOT NULL ,
  `test_id` INT NOT NULL ,
  `issue_name` VARCHAR(255) NOT NULL ,
primary key (sr_no,app_id, test_id)
);