The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
26.6k418575824

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.4k101132197
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
×47
×25
×3

question asked: 02 Aug '12, 07:00

question was seen: 1,923 times

last updated: 02 Aug '12, 14:12