Re: primary key & nlssort

On Sat, 30 Jun 2007 07:23:57 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>stvchien0_at_gmail.com wrote:
>> On Fri, 29 Jun 2007 14:00:00 -0700, DA Morgan <damorgan_at_psoug.org>
>> wrote:
>>
>>> stvchien0_at_gmail.com 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
>
>In addition:
>
>SQL> SELECT keyword
> 2 FROM gv$reserved_words
> 3 WHERE keyword IN ('ID', 'TYPE', 'MSG');
>
>KEYWORD
>------------------------------
>TYPE
>ID
>
>SQL>
>
>You are setting yourself up for failure.
>
>There is no room in a relational database for a column named 'ID' or
>'TYPE'. Perhaps what you intended was MSG_ID and MSG_TYPE.

  Tks, guys! We'll give it a try.

• Steve