在MySQL中使用自动增量定义复合键 [英] Defining Composite Key with Auto Increment in MySQL

问题描述

场景:

我有一个表，该表引用了两个外键，对于这些外键的每个唯一组合，都有自己的auto_increment列.我需要实现一个组合键，使用这三个键(一个外键和一个auto_increment列，以及另一个具有非唯一值的列)的组合，可以帮助将该行标识为唯一行

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)

表格:

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:

错误1075:错误的表格定义；只能有一辆汽车 列，并且必须将其定义为键

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

我要实现的目标:

我有一个应用程序表(以app_id作为其主键)，每个应用程序都有一组要解决的问题，并且每个应用程序都有多个测试(因此test_id col) sr_no col应该为唯一的app_id和test_id递增.

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:

数据库引擎是InnoDB. 我想尽可能简单地实现这一目标(即，尽可能避免使用触发器/程序-对于其他问题的类似案例也建议这样做).

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

推荐答案

对于InnoDB表，您不能让MySQL自动为您执行此操作-您需要使用触发器或过程，或者使用其他数据库引擎(例如MyISAM) .自动递增只能对单个主键完成.

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 ;

这篇关于在MySQL中使用自动增量定义复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！