Re: Unique and non unique index

Muthu wrote:
No the primary key index will always create the unique index.
The Non unique index are required to be created for faster data
retrieval and not for maintaining data integrity.

Your information is incorrect.
---------------------------------------------------------------------
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 15 08:52:21 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t (
2 col1 NUMBER(3),
3 col2 VARCHAR2(10));

Table created.

SQL> ALTER TABLE t
2 ADD CONSTRAINT pk_t
3 PRIMARY KEY (col1)
4 INITIALLY DEFERRED DEFERRABLE
5 USING INDEX

Table altered.

SQL> SELECT index_name, index_type, uniqueness
2 FROM user_indexes

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
PK_T NORMAL NONUNIQUE


Oracle can use either unique or non-unique indexes to support a primary
key constraint.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

Relevant Pages

  • Re: Duplicating data in continuous subform linked to SQL
    ... tables to a SQL 2008 DB that was upsized with Access 2003 to SQL 2005 then ... have a primary key or that Access has some trouble finding it or using it; ... using this second unique index as the primary key. ... you should take a look with the SQL-Server Profiler to see ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Index missing after exp with TRANSPORT_TABLESPACE=Y
    ... details I sent to Oracle to help them identify the problem. ... ALTER TABLE APTPYD_REC DROP CONSTRAINT APTPYDAPTPYH0; ... ALTER TABLE APTPYH_REC DROP PRIMARY KEY; ... CREATE UNIQUE INDEX PYH_KEY1 ON APTPYH_REC ...
    (comp.databases.oracle.server)
  • Re: Unique and non unique index
    ... Copyright 1982, 2005, Oracle. ... SQL> create table t ( ... PRIMARY KEY (col1) ... Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL ...
    (comp.databases.oracle.misc)
  • Re: Unable to edit forms data after refreshing a linked table
    ... The Oracle table does not have a primary key, ... >>I currently have linked tables to an Oracle database. ... > If you link to an ODBC table that does not have a Primary Key or Unique Index ... > If you do this and then later do a simple refresh link on that table the unique ...
    (microsoft.public.access.queries)
  • Re: MSSQL - Invalid attribute/option identifier
    ... So the DB is SQL Server now then? ... If a table does not have a primary key or unique index, ... from the Oracle DB and execute the scripts on SQL ... I have created this user PRV ...
    (microsoft.public.vc.mfc)
Loading