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
development=# CREATE TABLE foo1 (foo text REFERENCES foo);
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".
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.
Show quoted text
--
Regards,
Peter Geoghegan