Re: primary key & nlssort

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 30 Jun 2007 07:23:57 -0700
Message-ID: <1183213360.753557@bubbleator.drizzle.com>

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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jun 30 2007 - 09:23:57 CDT