Examples: Specifying the index associated with a constraint

donghuas-MacBook:~ donghua$ sqlplus donghua/donghua@orcl @create_emp1

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 15 22:37:46 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> create table emp (
  2  empno number(5) primary key,
  3  ssn number(5))
  4  enable primary key using index (create unique index emp_pk on emp(empno) tablespace users)
  5  /

Table created.

SQL> 
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME         CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK          SYS_C0012761

SQL> drop table emp purge;

Table dropped.

SQL> 
SQL> 
SQL> create table emp (
  2  empno number(5) primary key,
  3  ssn number(5))
  4  enable primary key using index tablespace users
  5  /

Table created.

SQL> 
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME         CONSTRAINT_NAME
------------------------------ ------------------------------
SYS_C0012762         SYS_C0012762

SQL> 
SQL> drop table emp purge;

Table dropped.

SQL> 
SQL> 
SQL> create table emp (
  2  empno number(5),
  3  ssn number(5),
  4  constraint emp_pk primary key(empno)
  5  )
  6  /

Table created.

SQL> 
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME         CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK          EMP_PK

SQL> drop table emp purge;

Table dropped.

SQL> 
SQL> create table emp (
  2  empno number(5),
  3  ssn number(5),
  4  constraint emp_pk primary key(empno) using index
  5        (create unique index emp_pk on emp(empno) tablespace users)
  6  )
  7  /

Table created.

SQL> 
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME         CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK          EMP_PK

SQL> drop table emp purge;

Table dropped.

SQL> 
SQL> 
SQL> create table emp (
  2  empno number(5),
  3  ssn number(5),
  4  constraint emp_pk primary key(empno) using index
  5        (create unique index emp_pk on emp(empno) tablespace users),
  6  constraint emp_unique_ssn unique(ssn) using index
  7        (create unique index emp_u1 on emp(ssn) tablespace users)
  8  )
  9  /

Table created.

SQL> 
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME         CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK          EMP_PK
EMP_U1          EMP_UNIQUE_SSN

SQL> drop table emp purge;

Table dropped.

SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options