Обсуждение: Case Insensitive Foreign Key Constraint
Hi all,
Is there a way to have text-type foreign keys be case insensitive?
development=# CREATE TABLE foo (foo text PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
development=# INSERT INTO foo VALUES ('foo');
INSERT 0 1
CREATE TABLE
development=# INSERT INTO foo VALUES ('foo');
INSERT 0 1
development=# CREATE TABLE foo1 (foo text REFERENCES foo);
CREATE TABLE
CREATE TABLE
development=# INSERT INTO foo1 VALUES ('FOO');
ERROR: insert or update on table "foo1" violates foreign key constraint "foo1_foo_fkey"
DETAIL: Key (foo)=(FOO) is not present in table "foo".
DETAIL: Key (foo)=(FOO) is not present in table "foo".
Thanks,
George
I would probably just have a check constraint that prevented the relevant PK field from being lower case in the first place. I had to do that recently, but my approach reflected the business rules. You may prefer to use citext: http://www.postgresql.org/docs/current/interactive/citext.html -- Regards, Peter Geoghegan
>From: Peter Geoghegan >Subject: Re: [GENERAL] Case Insensitive Foreign Key Constraint >I would probably just have a check constraint that prevented the >relevant PK field from being lower case in the first place. I had to >do that recently, but my approach reflected the business rules. This is what I've been looking at doing. My need reflects a customer's business rules as well. >You may prefer to use citext:> http://www.postgresql.org/docs/current/interactive/citext.html Interesting - good long term solution! Thanks Peter. >-- >Regards, >Peter Geoghegan