(PostgreSQL) Вопрос об ограничении расширенной проверки

Я использую PostgreSQL, но ищу максимально стандартный ответ SQL.

У меня есть следующая таблица "docs" --

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 version    | integer                | not null default 1
 link_id    | character varying(32)  | 
 content    | character varying(128) | 
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id, version)

id и link_id предназначены для документов, которые имеют отношение связи друг с другом, поэтому link_id сам ссылается на идентификатор.

Проблема связана с версией. Теперь id больше не является первичным ключом (также не будет уникальным) и на него нельзя ссылаться по link_id как на внешний ключ --

my_db=# ALTER TABLE docs ADD FOREIGN KEY(link_id) REFERENCES docs (id) ; 
ERROR:  there is no unique constraint matching given keys for referenced table "docs"

Я попытался найти проверочное ограничение на что-то вроде «если существует», но ничего не нашел.

Любая подсказка будет высоко оценена.


postgresql database constraints database-schema foreign-keys
person Jerry    schedule 06.03.2011    source источник

Ответы (3)


arrow_upward
4
arrow_downward

Я обычно делаю так:

table document (id, common, columns, current_revision)
table revision (id, doc_id, content, version)

что означает, что документ имеет отношение «один ко многим» со своими ревизиями и «один к одному» с текущей ревизией.

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

person jishi    schedule 06.03.2011

arrow_upward
1
arrow_downward

Придерживаясь как можно ближе к своей модели, вы можете разделить таблицу на две части, одна из которых имеет 1 строку на «документ», а другая — 1 строку на «версию»:

У вас есть следующая таблица "версии" -

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 version    | integer                | not null default 1
 content    | character varying(128) | 
Indexes:
    "versions_pkey" PRIMARY KEY, btree (id, version)

И следующая таблица "docs" --

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 link_id    | character varying(32)  | 
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)

в настоящее время

my_db=# ALTER TABLE docs ADD FOREIGN KEY(link_id) REFERENCES docs (id) ;

разрешено, и вы также хотите:

my_db=# ALTER TABLE versions ADD FOREIGN KEY(id) REFERENCES docs;

конечно, ничто не мешает вам получить «комбинированное» представление, похожее на вашу исходную таблицу:

CREATE VIEW v_docs AS
SELECT id, version, link_id, content from docs join versions using(id);
person Community    schedule 07.03.2011
comment
Спасибо, я хотел принять ваш ответ только для того, чтобы обнаружить, что SO имеет ограничение только на один принятый ответ на вопрос. Поскольку я больше склоняюсь к ответу Джиши (просто потому, что он ближе к моему дизайну приложения), все, что я могу сделать, это проголосовать за. Простите за это. - person Jerry; 07.03.2011
comment
Ответ @jishi хорош - я рад, что ты его принял. Возможно, вам потребуется узнать об ограничениях deferrable для реализации это так, как он предлагает - person ; 07.03.2011

arrow_upward
0
arrow_downward

В зависимости от того, что вы хотите, вы можете просто создать FOREIGN KEY, который включает поле версии. Это единственный способ указать на уникальную строку...

Если это не сработает, вы можете написать TRIGGER (для всех UPDATE и INSERT в таблице), который выполняет проверку. Обратите внимание, что вам также понадобится триггер для таблицы документов, который ограничивает изменения в этой таблице, которые могут нарушить ключ (например, DELETE или UPDATE для самого значения ключа).

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

person Magnus Hagander    schedule 06.03.2011