Re: primary key & nlssort


I think you're going to need two indexes - one to support the
primary key, and one to support the ordering/CI requirement.

drop table mytest;

CREATE TABLE MYTEST (
ID NUMBER(5,0) NOT NULL,
TYPE VARCHAR2(16) NOT NULL,
MSG VARCHAR2(1024) NOT NULL
);

CREATE UNIQUE INDEX MYTEST_2 ON MYTEST(ID, NLSSORT(TYPE,
'NLS_SORT=BINARY_CI'));
ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY(ID, TYPE);

This gets you uniqueness on the two different conditions whilst
still allowing you to do case-insensitive conditions on the TYPE
column.

With the following (on my 10.2.0.3)

set autotrace traceonly explain

select * from mytest
where id = 1 and type = 'ASD';

alter session set nls_sort = binary_ci;
alter session set nls_comp = ANSI;

select * from mytest
where id = 2 and type = 'ASD';

set autotrace off

I used the index pk_mytest for the first query,
but index mytest_2 for the second.

Note - I have created the nls index before the primary key
so that on an insert Oracle will test the uniqueness in that
order, minimising the cost of an attempted duplication.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


<stvchien0@xxxxxxxxx> wrote in message
news:598b83lj41dtjrm8uou1tlld1amj3pqb72@xxxxxxxxxx
On Fri, 29 Jun 2007 14:00:00 -0700, DA Morgan <damorgan@xxxxxxxxx>
wrote:

stvchien0@xxxxxxxxx wrote:
Hi,

Since Oracel will create indexe for PRIMARY KEY constraint, can we
use the NLSSORT with the column(s) which is(are) part of the PRIMARY
KEY?

Thanks!

- STeve

What version and please rewrite your inquiry and better describe what
you are asking. An example might be helpful.

Hi,

We're using Oracle 10g Release 2. Here is the example,

CREATE TABLE MYTEST
(ID NUMBER(5,0) NOT NULL,
TYPE VARCHAR2(16) NOT NULL,
MSG VARCHAR2(1024) NOT NULL);

CREATE UNIQUE INDEX PK_MYTEST ON MYTEST(ID, NLSSORT(TYPE,
'NLS_SORT=BINARY_CI'));

ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY(ID, TYPE);

We got 'ORA-00955: name is already used by an existing object'
error.

The other case didn't work either.

CREATE TABLE MYTEST
(ID NUMBER(5,0) NOT NULL,
TYPE VARCHAR2(16) NOT NULL,
MSG VARCHAR2(1024) NOT NULL);

alter table mytest add constraint PK_mytest primary key(ID,
NLSSORT(TYPE, 'NLS_SORT=BINARY_CI'));

We got 'ORA-00904: : invalid identifier' error.

Hopefully, this example could show what we would like to achieve.
Thanks!

- Steve


.

Relevant Pages

  • Re: primary key & nlssort
    ... Since Oracel will create indexe for PRIMARY KEY constraint, ... ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY ...
    (comp.databases.oracle.server)
  • Re: primary key & nlssort
    ... Since Oracel will create indexe for PRIMARY KEY constraint, ... ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY ...
    (comp.databases.oracle.server)
  • Re: primary key & nlssort
    ... Since Oracel will create indexe for PRIMARY KEY constraint, ... ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY ...
    (comp.databases.oracle.server)
  • SQL Server confused about primary keys. So am I.
    ... We are generating a script to convert a client's database to Sql ... ALTER TABLE dbo.Categories ADD ... CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.server)
  • Re: Alter table help
    ... alter table table_name add constraint constraint_name primary key ... > "Tom Pennington" wrote in message ... >> Okay, I'm trying to modify a tables primary key, actually, I'm trying to ...
    (microsoft.public.sqlserver.programming)