Определение составного ключа с автоматическим приращением в MySQL

Сценарий:

У меня есть таблица, которая ссылается на два внешних ключа, и для каждой уникальной комбинации этих внешних ключей есть свой собственный столбец auto_increment. Мне нужно реализовать составной ключ, который поможет идентифицировать строку как уникальную, используя комбинацию этих трех (один внешний ключ и один столбец auto_increment, а также один другой столбец с неуникальными значениями)

Таблица: 

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

Конечно, что-то не так с моим запросом, потому что из которых возникает ошибка:

Ошибка 1075: неверное определение таблицы; может быть только одно авто столбец и он должен быть определен как ключ

Чего я пытаюсь достичь:

У меня есть таблица приложений (с app_id в качестве первичного ключа), каждое приложение имеет набор проблем, которые должны быть решены, и каждое приложение имеет несколько тестов (поэтому test_id col) Sr_no col должен увеличиваться для уникальных app_id и test_id.

То есть данные в таблице должно выглядеть так:

Введите описание изображения здесь

Компонент database engine-InnoDB. Я хочу достичь этого с максимально возможной простотой (т. е. избегать триггеров / процедур, если это возможно - что было предложено для аналогичных случаев по другим вопросам).

4 17
mysql auto-increment innodb composite-key

4 ответа:

Вы не можете заставить MySQL делать это автоматически для таблиц InnoDB - вам нужно было бы использовать триггер или процедуру, или использовать другой движок БД, такой как MyISAM. Автоматическое увеличение может быть сделано только для одного первичного ключа.

Должно сработать что-то вроде следующего 

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

Это можно сделать с помощью движков myISAM и BDB. InnoDB не поддерживает это. Цитата из справочного руководства MySQL 5.0.

Для таблиц MyISAM и BDB можно указать AUTO_INCREMENT для дополнительного столбца в многоколоночном индексе. В этом случае генерируемое значение для столбца AUTO_INCREMENT вычисляется как MAX (auto_increment_column) + 1, где prefix=given-prefix.

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

7

Вы можете использовать уникальный составной ключ для sr_no,app_id & test_id. Вы не можете использовать incremental в sr_no, так как это не уникально.

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 ;

Я прокомментировал нарушение уникального ограничения в скрипка sql чтобы продемонстрировать (удалить # в строке 22 схемы и перестроить схему)

3

Я не совсем понимаю ваше требование инкремента в столбце test_id, но если вы хотите последовательность ~ autoincrement, которая перезапускается при каждой уникальной комбинации (app_id, test_id), Вы можете сделать вставку ... Выберите из той же таблицы, например: 

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 */

Это предполагает определение таблицы без объявленного столбца AUTO_INCREMENT. По сути, вы эмулируете поведение автоинкремента с помощью предложения IFNULL (MAX ()) + 1, но ручная эмуляция работает на произвольных Столбцах, в отличие от встроенного Автоинкремент.

Обратите внимание, что вставка ... SELECT, являющийся одним запросом, обеспечивает атомарность операции. InnoDB заблокирует соответствующий индекс, и многие параллельные процессы могут выполнить этот вид запроса, все еще производя неконфликтные последовательности.

3