My situation is the following: suppose you have something like:

CREATE TABLE t1(
  mykey VARCHAR(255) primary key,
  myvalue VARCHAR(255)
);

Since Sybase is case-sensitive over the column values, something like this would be allowed:

INSERT INTO t1(mykey, myvalue) VALUES('key', 'value');
INSERT INTO t1(mykey, myvalue) VALUES('KEY', 'VALUE');

Result:

+-----+-------+
| key | value |
+-----+-------+
| KEY | VALUE |
+-----+-------+

I actually want to prevent this, and the second insertion should fail for duplicated key. Anyway, if I want to modify the value of any other column which is not primary key, for example from 'value' to 'VaLuE', I want to be able to do that, preserving the case insensitivity constraint only on the primary key.

This is possible on Oracle by creating an upper/lower case index on the table:

CREATE INDEX myindex ON t1(UPPER(mykey));

Anyway this doesn't seem to work on Sybase, since the duplicated-case insertion is still allowed.

I've found some hints around the web which would act on the DBMS configuration, through sp_configure/sp_text_configure, but I want to avoid such an intrusive solution, and apply the case sensitivity ONLY to some specific tables and ONLY on the primary keys of those tables, like I do on Oracle by creating the index through the syntax shown above.

Is this possible on Sybase?

asked 02 Aug '12, 07:00

FabioManganiello's gravatar image

FabioMangani...
20115
accept rate: 0%

closed 02 Aug '12, 14:12

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844

Comment Text Removed

The question has been closed for the following reason "Question is off-topic or not relevant" by Breck Carter 02 Aug '12, 14:12


Are you talking about SQL Anywhere? By default SQL Anywhere databases are case insensitive - so the insert you show would fail:

alt text

If you have created a case sensitive database, you could would have to do something either with a check constraint on the table or possibly a trigger that would reject the insert.

permanent link

answered 02 Aug '12, 07:50

Justin%20Willey's gravatar image

Justin Willey
6.7k108138208
accept rate: 20%

Hi,

Thanks for your answer. Can you please provide me an example of possible trigger for implementing such a policy? (I think I would opt for this solution).

Thanks again,

-- Fabio

p.s. I know that using SQL Anywhere the insertion would fail, but I would like to have an all-purpose solution even in case of an external application which interfaces with Sybase through any driver.

(02 Aug '12, 08:58) FabioMangani...
Replies hidden

What version of SQL Anywhere are you using?

SELECT @@Version

will tell you.

(02 Aug '12, 09:07) Justin Willey

Adaptive Server Enterprise/15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/FBO/Mon Nov 9 18:44:45 2009

(02 Aug '12, 10:17) FabioMangani...

You are not using SQL Anywhere; you are using ASE. This forum is for questions about SQL Anywhere. You are better off asking your question on one of the ASE forums - see http://www.sybase.com/detail_list?id=11507 for a list - perhaps sybase.public.ase.general (http://www.sybase.com/detail?id=1012843)?

(02 Aug '12, 10:46) Mark Culp
Comment Text Removed

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×48
×26
×3

question asked: 02 Aug '12, 07:00

question was seen: 2,001 times

last updated: 02 Aug '12, 14:12