CODEWITHSUNDEEP
searchindexingprimary-keyfirebirdcase-insensitive
durumdara
durumdara

Reputation: 3463

FireBird: combine upper with primary key constraint

I want to use case insensivity in more tables which came from other DB where the fields and indexes can be case insensitive.

This means that we can search the needed row in any string format (DAta, Data, data, etc.), we can find that by any of these keys.

I tried to use upper function with index, and use this in a primary key to preserve the program logic.

But I failed with it. I didn't find any valid SQL statement to define it.

Maybe it's an impossible mission? Or you know which ways I define Primary Key with "upper" index?

Thanks for any info!

Upvotes: 1

Views: 300

Answers (2)

rstrelba
rstrelba

Reputation: 1954

IMHO better way is to use index by expresion

create index idx_upper on persons computed by (upper(some_name))

sql queries

select * from persons order by upper(some_name);
select * from persons where upper(some_name) starting with 'OBAM';

will use index idx_upper

Upvotes: 0

ain
ain

Reputation: 22749

If you want to do case insensitive search you're supposed to use case insensitive collation. In case you always want to treat the field's value in case insensitive manner you should define it at the field level, ie

CREATE TABLE T (
  Foo VARCHAR(42) CHARACTER SET UTF8 COLLATE UNICODE_CI,
  ...
)

but you can also specify the collation at the search like

SELECT * FROM T WHERE Foo = 'bar' COLLATE UNICODE_CI

Read more about available collations at the Firebird's language reference.

Upvotes: 3

Related Questions