Определение составного ключа с автоматическим добавлением в 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.

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

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

Ответ 1

Вы не можете заставить 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 ;

Ответ 2

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

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

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

Ответ 3

Вы можете использовать составной ключ уникальный для 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 fiddle, чтобы продемонстрировать (удалить # в строке 22 схемы и перестроить схему)

Ответ 4

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

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'
       WHERE `app_id` = 3 AND `test_id` = 1 /* same values as in inserted columns */

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

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