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');
+-----+-------+ | 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?
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:
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.
answered 02 Aug '12, 07:50