By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Problems with unique key constraint.(CASE INSENSITIVE)

P: 17
I'm stack with this problem with some time now.

Here is my problem.

CREATE TABLE category
(
catdb_id bigserial NOT NULL,
catdb_name character varying,
catdb_remarks character varying,
CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
)

INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted

How can i make this two values which i inserted above to be equal? Any solution. Thank you.
Sep 23 '07 #1
Share this Question
Share on Google+
6 Replies


amitpatel66
Expert 100+
P: 2,367
I'm stack with this problem with some time now.

Here is my problem.

CREATE TABLE category
(
catdb_id bigserial NOT NULL,
catdb_name character varying,
catdb_remarks character varying,
CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
)

INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted

How can i make this two values which i inserted above to be equal? Any solution. Thank you.
Convert the value to either UPPER or lower case everytime you do an insert because the data inserted in different case will not be the same
Sep 24 '07 #2

P: 17
Thanks for the reply.

I already do that. But i want to retain the original values or position that is being typed by the user. What will be my other solution? any suggestion? Thanks again.
Sep 24 '07 #3

Expert 100+
P: 700
I'm stack with this problem with some time now.

Here is my problem.

CREATE TABLE category
(
catdb_id bigserial NOT NULL,
catdb_name character varying,
catdb_remarks character varying,
CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
)

INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted

How can i make this two values which i inserted above to be equal? Any solution. Thank you.

Creating index should be enough
CREATE UNIQUE INDEX in_category_cs ON category ((lower(catdb_name)))
Sep 24 '07 #4

P: 17
What if the user will insert this three value.
INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dyChaRleS')
---- successfully inserted

How can they be equal in unique constraint or in unique index? Thanks
Sep 24 '07 #5

Expert 100+
P: 700
What if the user will insert this three value.
INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dyChaRleS')
---- successfully inserted

How can they be equal in unique constraint or in unique index? Thanks
They are equal because index is using the lower() function.
Sep 24 '07 #6

P: 17
Thanks, great help.

More Power
Sep 24 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.