Как создать автоматически увеличивающийся номер ревизии, уникальный для ключа в PGSQL?

Предположим, что у меня есть следующие таблицы.

PARENT: PARENT_ID serial, DESCRIPTION character varying(50)

CHILD: PARENT_ID integer, CHILD_ID integer, DESCRIPTION character varying(50)

Я хотел бы видеть, что каждая строка в CHILD имеет CHILD_ID, который начинается с 1 и увеличивается на 1, уникальный для PARENT_ID. Это будет похоже на номер редакции. Например..

PARENT_ID 1, CHILD_ID 1
PARENT_ID 1, CHILD_ID 2
PARENT_ID 1, CHILD_ID 3
PARENT_ID 2, CHILD_ID 1
PARENT_ID 3, CHILD_ID 1
PARENT_ID 3, CHILD_ID 2

Существует ли какой-либо способ автоматического назначения значения CHILD_ID, например последовательности или ограничения, только с возможностью повторного использования child_id, который был удален? Единственный способ, который я могу понять, - это что-то в этом роде. язык SQL.

INSERT INTO child SELECT parent_id, MAX(child_id)+1, 'description' FROM child WHERE parent_id = :PARENT_ID GROUP BY parent_id

Это немного халтура, хотя. Я понимаю, что нормализация базы данных предполагает, что вы не должны иметь один ключ, связанный с другим, но у меня нет такой возможности по некоторым другим причинам. Есть идеи?

Редактировать: название уродливое. Если кто-то из вас, высоко оценивающих людей, может придумать более точный вариант, пожалуйста, не стесняйтесь его менять.

3 2
sql postgresql constraints

3 ответа:

Я бы предложил использовать: 

CHILD: PARENT_ID integer, CHILD_ID serial, DESCRIPTION character varying(50)

Когда вам нужно получить желаемый результат:

  • Вы можете считать строки на стороне клиента.

  • При выборе строк, где PARENT_ID=? вы можете использовать временную последовательность.

  • В скором выпуске Postgresql 8.4 вы можете использовать оконные функции, такие как: 

    $ create table child (parent_id integer, child_id serial);
NOTICE:  CREATE TABLE will create implicit sequence "child_child_id_seq" for serial column "child.child_id"
CREATE TABLE

$ insert into child (parent_id) values (1), (1), (1), (2), (3), (3);

$ select * from child;
 parent_id | child_id 
-----------+----------
         1 |        1
         1 |        2
         1 |        3
         2 |        4
         3 |        5
         3 |        6
(6 rows)

$ select parent_id, row_number() over (partition by parent_id order by child_id) from child;
 parent_id | row_number 
-----------+------
         1 |          1
         1 |          2
         1 |          3
         2 |          1
         3 |          1
         3 |          2
(6 rows)

Это очень быстро, легко реализовать и будет масштабироваться Очень хорошо, так как не будет никаких проблем параллелизма, чтобы беспокоиться.

2

Эта вставка-еще не вся история. Вам также нужно будет обработать удаление, чтобы закрыть разрыв, который был создан, если вы действительно хотите, чтобы числа были смежными.

Мое предложение состояло бы в том, чтобы вывести это значение, как вам это нужно. Что определяет порядок следования чисел? Если это дата, введенная в систему, то добавьте эту дату в свою таблицу и поместите свой PK поверх parent_id и этой даты, тогда вы можете довольно легко придумать число либо через SQL, либо в интерфейсе как тебе это нужно.

0

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

BEGIN
-- Get and hold onto parent_id and version values.
SELECT PARENT_ID, VERSION FROM PARENT WHERE PARENT_ID = :PARENT_ID;
-- Use the values to insert into the child table
INSERT INTO CHILD (PARENT_ID, CHILD_ID) VALUES (:PARENT_ID, :VERSION);
-- Update the version using an optimistic lock.
UPDATE PARENT SET VERSION = VERSION + 1 WHERE PARENT_ID = :PARENT_ID AND 
                                              VERSION = :VERSION_ID
-- If no rows are updated rollback the transaction and try again.
END

Это гарантирует, что дочерние идентификаторы строго возрастают, но не будут повторно использовать значения идентификаторов после удаления. Если вы можете избежать ограничения повторного использования старых идентификаторов, это упростит ваше решение (и решение будет более эффективным). Если вы должны повторное использование идентификаторов тогда у вас есть 2 варианта, Во-первых решение, которое вы указали выше, но при удалении перенумеровать все значения, которые происходят после того, как вы удалили. Другой вариант-иметь какую-то функцию, которая сканирует дочерние идентификаторы по порядку и сравнивает их с набором последовательных чисел и возвращает значение, когда первый не найден. Оба эти решения являются более сложными и будут медленными, так как вам нужно будет снять блокировку строк, чтобы предотвратить одновременное обновление и либо вставки или как вставки, так и удаления будут нести штраф O(n).

0