Два первичных ключа столбца, столбец с автоинкрементом в зависимости от значения 2-го столбца

В Firebird это не так просто сделать, как в MySQL. Если количество database_id известно заранее, вы можете выделить последовательность для каждого идентификатора и использовать это в триггере, но это быстро становится громоздким для большого количества идентификаторов.

Остальная часть моего ответа предполагает использование Firebird 2.5 (я тестировал его с Firebird 2.5.2 Update 1).

Если у нас есть только database_ids 1 и 2, мы можем создать две последовательности:

CREATE SEQUENCE multisequence_1;
CREATE SEQUENCE multisequence_1;

Нам нужно исключение, когда используется идентификатор, не имеющий последовательности:

CREATE OR ALTER EXCEPTION no_sequence 'No corresponding sequence found';

Затем мы можем использовать следующий триггер:

CREATE OR ALTER TRIGGER multisequence_BI FOR multisequence
   ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
   IF (NEW.database_id = 1) THEN
       NEW.table_id = NEXT VALUE FOR multisequence_1;
   ELSE IF (NEW.database_id = 2) THEN
       NEW.table_id = NEXT VALUE FOR multisequence_2;
   ELSE 
       EXCEPTION no_sequence;
END

Как видите, это быстро приведет к множеству операторов IF / ELSE. Вероятно, это можно упростить, используя _ 6_ и динамически сгенерированный запрос для следующего значения последовательности. Это не сработает, если вы не можете заранее контролировать количество database_id значений (и их последовательности).

Вы можете попытаться решить эту проблему с помощью динамических запросов, как показано ниже. Это может иметь свои собственные проблемы (особенно при большом объеме вставок), потому что EXECUTE STATEMENT имеет некоторые накладные расходы, а также может привести к проблемам из-за использования динамического DDL (например, конфликты блокировок / обновлений в таблицах метаданных).

CREATE OR ALTER TRIGGER multisequence_BI FOR multisequence
   ACTIVE BEFORE INSERT POSITION 0
AS
   DECLARE new_id INTEGER;
   DECLARE get_sequence VARCHAR(255);
BEGIN
    get_sequence = 'SELECT NEXT VALUE FOR multisequence_' || NEW.database_id || 
         ' FROM RDB$DATABASE';
    BEGIN
        EXECUTE STATEMENT get_sequence INTO :new_id;
        WHEN SQLCODE -104 DO
        BEGIN
            EXECUTE STATEMENT 
                'CREATE SEQUENCE multisequence_' || NEW.database_id 
                WITH AUTONOMOUS TRANSACTION;
            EXECUTE STATEMENT get_sequence INTO :new_id;
        END
    END
    NEW.table_id = new_id;
END

Этот код по-прежнему уязвим для нескольких транзакций, пытающихся создать одну и ту же последовательность. Добавление WHEN ANY DO после оператора, который (пытался) создать последовательность, может позволить вам использовать последовательность в любом случае, но это также может привести к ложным ошибкам, таким как конфликты блокировок. Также обратите внимание, что использование DDL в EXECUTE STATEMENT не рекомендуется (см. предупреждение в документации).

Прежде чем использовать это решение в производственной ситуации, я настоятельно рекомендую тщательно протестировать его под нагрузкой!

Обратите внимание, что предложение WITH AUTONOMOUS TRANSACTION технически не требуется для создания последовательности, но необходимо, чтобы последовательность была также видима для других транзакций (и не удаляется при откате исходной транзакции).

Также помните о максимальном количестве последовательностей (или: генераторов) в одной базе данных Firebird: +/- 32758, см. Руководство по генераторам Firebird: сколько генераторов доступно в одной базе данных?.

Поле «table_id» на самом деле представляет собой простую нумерацию строк, основанную на порядке вставки записей и разделенную по «database_id». Первый "database_id" foo получает "table_id", равный 1, второй foo - 2, первый bar - 1, второй < em> bar a 2 и т. д.

Это можно вычислить динамически, если у вас есть способ узнать порядок вставки строк для каждого «database_id». Обычный столбец с автоинкрементом, применяемый ко всем строкам, дает вам такой порядок. Тогда вычисления могут быть скрыты за ВИДОМ, и ваше приложение не должно быть мудрее.

Номера секционированных строк легко выразить с помощью оконных функций SQL, которые поддерживаются в Firebird 3, если я не ошибаюсь:

SELECT ROW_NUMBER() OVER (PARTITION BY "database_id" ORDER BY auto-increment-column ) AS "table_id"

Для Firebird 2 вы можете вычислить это самостоятельно, задав для каждого отдельного «database_id» количество строк, предшествующих ему:

   SELECT COUNT(b.auto_inc_id) + 1 AS table_id,
          a.database_id
     FROM tbl a
LEFT JOIN tbl b
          ON a.database_id = b.database_id AND b.auto_inc_id < a.auto_inc_id
 GROUP BY 2