Re: migrating - index question

tsukkel_at_vmsmail.gov.bc.ca wrote:
: Hello,
: I am assisting in migrating a Database to Oracle7 and am having trouble
: with indexes. On page 4-160 of the Oracle7 Server, SQL language reference
: manual I noticed that the use of the qualifer 'Unique' is missing. Is this a
: documentation issue or must you use the the Primary Key construct to attain
: this? What if I have two unique keys; ie; Emp_id and SIN? The index I am trying
: to port is a UNIQUE DESC index. I also noted that on page 4-160 as noted above,
: that 'Desc' on the create index statement is allowed for syntax parsing only and
: does not create a descending index. What options do I have to migrate this
: schema to Oracle?
 

: Tony Sukkel

Hi Tony,

Although, page 4-160 does not mention the qualifier UNIQUE, it is possible to create UNIQUE indexes. The syntax is Create UNIQUE Index <Index_Name> on <Table_Name> (<column>...16). The documentation is incorrect.

There is a difference between a Primary constraint and a unique constrain. There can only be one primary constraint; however, there can be many UNIQUE constraints.

A Primary constraint requires all the columns of the constraint to be NOT NULL; A unique constraint does not impose this restriction on its columns.

Both Primary and unique constraints implicitly create unique indexes, the same that you can create using the Create Unique index statement.

Since Oracle lets you control the sequence of retrieval of records, only by the ORDER BY clause, it does not let you have a descending index. The clause descending is available only for DB2 compatibility. clause

In short, you can create a unique index but you cannot create the index in descending order.

Hope this helps.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
       ** Different is Not Better. Better is not Different. **
---------------------------------------------------------------------------