нечувствительный к регистру внешний ключ PostgreSQL

Нужно знать, могу ли я добавить ограничение внешнего ключа без учета регистра в таблицу в PostgreSQL. Я конвертирую БД из MySQL в PostgreSQL. Внешние ключи уже были созданы в MySQL, и я не столкнулся с какими-либо проблемами, потому что MySQL не чувствителен к регистру. Когда я загружаю все данные из MySQL в PostgreSQL, а затем пытаюсь добавить внешние ключи в таблицы, я получаю ошибку в Postgres. Например:

В таблице A было 2 столбца: ID (int) и Name (varchar (25))

Entry1: ID = 1 , Name = 'America' Entry2: ID = 2 , Name = 'Canada'

В таблице B было 2 столбца: ID (int) и Name (varchar (25))

Entry1: ID(int) = 10 , Name(Varchar(25)) = 'AmeRiCA' Entry1: ID = 1 , Name = 'Canada'

в MySQL внешний ключ был создан между таблицей A и таблицей B в столбце «Имя», однако в Postgres из-за чувствительного к регистру характера я получаю сообщение об ошибке.

У меня нет возможности изменить таблицу и изменить Varchar на citext. в любом случае я могу определить регистронезависимый внешний ключ в PG.

Какие-либо предложения?

Спасибо


postgresql sql case-sensitive postgresql-9.3
person WinSupp    schedule 15.10.2014    source источник
comment
Ваш вопрос интересен, но мне любопытно... Почему вы предпочитаете использовать имя в качестве ключа (для создания fk) вместо идентификатора?   -  person Jorge Campos    schedule 15.10.2014
comment
@JorgeCampos - Ну, это тот же вопрос, который пришел мне в голову, когда я начал смотреть на эту БД. Эта БД уже была создана и передана мне. Итак, как вы думаете, есть ли способ выполнить эту задачу?   -  person WinSupp    schedule 15.10.2014
comment
Я думаю, что без опции citext вам пришлось бы создать триггер перед вставкой/обновлением/удалением, чтобы проверить, существуют ли эти строки, используя нижние или верхние функции. Я знаю, это некрасиво!   -  person Jorge Campos    schedule 15.10.2014

Ответы (1)


arrow_upward
2
arrow_downward

Безусловно, лучшее, что вы можете сделать, это исправить свои данные. Например вот так:

update A set name=initcap(name) where name<>initcap(name);
update B set name=initcap(name) where name<>initcap(name);

Если это неприемлемо, вы можете создать дополнительный столбец в обеих таблицах, скажем, name_lower, который будет автоматически установлен в lower(name) с помощью триггера:

create or replace function name_lower_trigger() returns trigger as $$
  begin
    NEW.name_lower=lower(name);
    return NEW;
  end;
$$ language plpgsql;

create trigger a_name_lower_trigger
  before insert or update on a
  for each row execute procedure name_lower_trigger();
create trigger b_name_lower_trigger
  before insert or update on b
  for each row execute procedure name_lower_trigger();

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

person Tometzky    schedule 15.10.2014
comment
Это практически единственное разумное решение. Единственной другой альтернативой может быть реализация ваших собственных триггеров внешнего ключа, выполняющих принудительное сравнение строчных букв или что-то в этом роде. - person Richard Huxton; 16.10.2014