Определить внешний ключ в Postgres для подмножества целевой таблицы

Question

Пример:
Я имею:

Table A:  
 int id  
 int table_b_id

Table B:  
 int id  
 text type  

Я хочу добавить проверку ограничения на столбец table_b_id, который будет проверять, что он указывает только на строки в таблице B, значение их типа которых равно 'X'.
Я не могу изменить структуру таблицы.
Я понял, что это можно сделать с помощью 'CHECK' и функций postgres, которые будут выполнять конкретный запрос, но я видел людей, которые рекомендовали избегать этого.
Любые материалы о том, что является лучшим подходом для его реализации, будут полезны.

Answer 1

То, что вы имеете в виду, не является FOREIGN KEY, который в PostgreSQL ссылается на (число) столбцов в другой таблице, где существует уникальный индекс для этих / этих столбцов, и которые могут иметь связанные автоматические действия, когда значение (я) что / те столбцы изменяются (ON UPDATE, ON DELETE).

Вы пытаетесь обеспечить конкретную ссылочную целостность, аналогичную FOREIGN KEY делает. Вы можете сделать это с CHECK предложение и функция (потому что CHECK предложение не допускает подзапросов), вы также можете сделать это с наследованием таблицы и разделением диапазона (обратитесь к дочерней таблице, которая содержит только те строки, где type = 'X'), но это, вероятно, проще всего сделать с помощью триггера:

CREATE FUNCTION trf_test_type_x() RETURNS trigger AS $$
BEGIN
  PERFORM * FROM tableB WHERE id = NEW.table_b_id AND type = 'X';
  IF NOT FOUND THEN
    -- RAISE NOTICE 'Foreign key violation...';
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE tr_test_type_x
BEFORE INSERT OR UPDATE ON tableA
FOR EACH ROW EXECUTE PROCEDURE trf_test_type_x();

Вы можете создать частичный индекс на tableB чтобы ускорить процесс:

CREATE UNIQUE INDEX idx_type_X ON tableB(id) WHERE type = 'X';

Answer 2

На мой взгляд, наиболее элегантным решением является использование наследования для получения поведения подтипа:

Настройка схемы PostgreSQL 9.3 с наследованием:

create table B ( id int primary key );

-- Instead to create a 'type' field, inherit from B for
-- each type with custom properties:
create table B_X ( -- some_data varchar(10 ),
                   constraint pk primary key (id)
                 ) inherits (B);

-- Sample data:
insert into B_X (id) values ( 1 );
insert into B (id)   values ( 2 );

-- Now, instead to reference B, you should reference B_X:
create table A ( id int primary key, B_id int references B_X(id) );

-- Here it is:
insert into A values ( 1, 1 );

--Inserting wrong values will causes violation:
insert into A values ( 2, 2 );    

ОШИБКА: вставка или обновление таблицы "a" нарушает ограничение внешнего ключа "a_b_id_fkey" Подробно: Ключ (b_id)=(2) отсутствует в таблице "b_x".

Извлечение всех данных из базовой таблицы:

select * from B

Результаты:

| id |
|----|
|  2 |
|  1 |

Получение данных с типом:

SELECT p.relname, c.*
FROM B c inner join pg_class p on c.tableoid = p.oid

Результаты:

| relname | id |
|---------|----|
|       b |  2 |
|     b_x |  1 |