Monday, November 15, 2010

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