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).

Grandeur

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 use 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 ;

Formosa

Ok, and what changes should I do in my Create Table query? I can't make sr_no a Primary Key because its not gonna be unique. And I can't make it Autoincrement without making it Primary Key... – Grandeur

Just take the autoincrement off - it can't do what you want. The primary key stays the same. – Formosa

I am sorry I haven't worked with triggers, and this Trigger code is throwing out "Syntax Error" - with nothing else defined. Can you please clean up the code, and once done, put a comment here? The error is: ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.sr_no=(select ifnull(select max(sr_no)+1 from issue_log where app_id=new.app_id' at line 3 – Grandeur

Sorry there was a missing SET - I have added it into the text. – Formosa

Still and error; the error is on the line starting with "set..." and on second select, it shows "unexpected SELECT_SYM" syntax error. – Grandeur

Unbalanced brackets! Fixed in the answer. – Formosa

Thanks @noz. Will check it out! :) – Grandeur

I tried this solution and it didn't work for me. I wanted some auto_increment column based on another column. This would end up creating a deadlock. The solution I ended up going with looks like this. gist.github.com/russjohnson09/02323c148b60ed7805ce644340974a0c – Limn

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

Distraught

Just for your information, quoting the relevant information along with a link is always preferred on Stack Overflow, answers can be deleted for only referring the question asker to a link. – Peppercorn

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.

Marxist

The project is over. But hopefully this will help someone who stumble across. I am not sure of my question back then :P – Grandeur

Insert-select isn't necessarily atomic. It depends on your locking settings/isolation level. See #21438533, or dba.stackexchange.com/questions/73540/…. – Matson

I'm afraid of this: I make an entity with pk (1, 7) (since the highest number will be 6), make a foreign key to it and then delete it. Then once I add an entity, it will have pk (1, 7) too and all those dangling things will now connect to it via an already existing foreign key to (1, 7). – Chesson

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 )

Larcher

Answer 5

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)
);

Execrative

Answer 6

This is what I wanted

   id    tenant
    1        1
    2        1
    3        1
    1        2
    2        2
    3        2
    1        3
    2        3
    3        3

My current table definition is

CREATE TABLE `test_trigger` (
  `id` BIGINT NOT NULL,
  `tenant` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`tenant`)
);

I created one table for storing the current id for each tenant.

CREATE TABLE `get_val` (
  `tenant` varchar(255) NOT NULL,
  `next_val` int NOT NULL,
  PRIMARY KEY (`tenant`,`next_val`)
) ENGINE=InnoDB ;

Then I created this trigger which solve my problem

DELIMITER $$
CREATE TRIGGER trigger_name 
BEFORE INSERT 
ON test_trigger 
FOR EACH ROW
    BEGIN
        UPDATE get_val SET next_val = next_val + 1 WHERE tenant = new.tenant;
        set new.id = (select next_val from get_val where tenant=new.tenant);
      END$$
    DELIMITER ;

This approach will be thread safe also because any insertion for the same tenant will happen sequentially because of the update query in the trigger and for different tenants insertions will happen parallelly.

Dorsum

Answer 7

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`)
);

Airfoil