RE: naming conventions (constraints and indexes)

The table creation was actually taken from an example where Tom Kyte responded 
to a question as to the best way/order to create the index/constratint, 28 Apr 
2008.  
http://asktom.oracle.com/pls/asktom/f?p=100:11:1892071625408060::::P11_QUESTION_ID:36858373078604

However, as part of this process I will review a best practices method of 
creating these things as has been pointed out in a couple places.

Personally, I have been kind of living with the index drop issue for a long 
long time, so it wasn't a major concern.  I think I'm going to return and 
review the issue again while I'm on the subject of naming and standards.



I cannot seem to get any rows returned from your select from dba_objects query 
10.2.0.4 or 11.2.0.1.




DBMON  @ lawrtdev> CREATE TABLE EXAMPLE_TABLE (
  2    PRIM_COL number constraint EXAMPLE_TABLE_PK primary key
  3      using index ( CREATE INDEX EXAMPLE_TABLE_PX ON
  4        EXAMPLE_TABLE(PRIM_COL)),
  5    UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE
  6      using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on
  7        EXAMPLE_TABLE(UNIQ_COL)),
  8    junk varchar2(10)
  9  );

Table created.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> create table t (veld1 number(10));

Table created.

DBMON  @ lawrtdev> insert into t values (1);

1 row created.

DBMON  @ lawrtdev> insert into example_table values (1,1,'Example');

1 row created.

DBMON  @ lawrtdev> commit;

Commit complete.

DBMON  @ lawrtdev> alter table t add constraint t_pk primary key (veld1)
  2       using index (create unique index t_i on t (veld1));

Table altered.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> select index_name, index_type, uniqueness, generated
  2      from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');

INDEX_NAME                     INDEX_TYPE                  UNIQUENES G
------------------------------ --------------------------- --------- -
EXAMPLE_TABLE_UNIQ_COL_UX      NORMAL                      NONUNIQUE N
EXAMPLE_TABLE_PX               NORMAL                      NONUNIQUE N
T_I                            NORMAL                      UNIQUE    N

3 rows selected.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> select constraint_name, constraint_type, index_name, 
generated
  2      from user_constraints where table_name in ('T', 'EXAMPLE_TABLE');

CONSTRAINT_NAME                C INDEX_NAME                     GENERATED
------------------------------ - ------------------------------ --------------
EXAMPLE_TABLE_PK               P EXAMPLE_TABLE_PX               USER NAME
EXAMPLE_TABLE_UNIQ_COL_UK      U EXAMPLE_TABLE_UNIQ_COL_UX      USER NAME
T_PK                           P T_I                            USER NAME

3 rows selected.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> select
  2      obj_t.owner table_owner, obj_t.object_name table_name,
  3      obj_i.owner index_owner, obj_i.object_name index_name,
  4      decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
  5      decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
  6    from
  7      dba_objects   obj_t,
  8      dba_objects   obj_i,
  9      sys.ind$      ind
 10    where
 11      ind.bo# = obj_t.object_id
 12      and ind.obj# = obj_i.object_id
 13      and obj_i.owner = 'FDH'
 14      and obj_i.object_name = 'T_I'
 15  --          ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX')
 16    order by table_name, index_name ;

no rows selected

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> alter table t drop constraint t_pk;

Table altered.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> select index_name from user_indexes where table_name in 
('T', 'EXAMPLE_TABLE');

INDEX_NAME
------------------------------
EXAMPLE_TABLE_UNIQ_COL_UX
EXAMPLE_TABLE_PX

2 rows selected.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> alter table t add primary key (veld1)
  2        using index (create unique index t_i on t (veld1));

Table altered.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> select index_name, index_type, uniqueness, generated
  2      from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');

INDEX_NAME                     INDEX_TYPE                  UNIQUENES G
------------------------------ --------------------------- --------- -
EXAMPLE_TABLE_UNIQ_COL_UX      NORMAL                      NONUNIQUE N
EXAMPLE_TABLE_PX               NORMAL                      NONUNIQUE N
T_I                            NORMAL                      UNIQUE    Y

3 rows selected.

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> select
  2      obj_t.owner table_owner, obj_t.object_name table_name,
  3      obj_i.owner index_owner, obj_i.object_name index_name,
  4      decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
  5      decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
  6    from
  7      dba_objects   obj_t,
  8      dba_objects   obj_i,
  9      sys.ind$      ind
 10    where
 11      ind.bo# = obj_t.object_id
 12      and ind.obj# = obj_i.object_id
 13      and obj_i.owner = 'FDH'
 14      and obj_i.object_name in
 15            ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX')
 16    order by table_name, index_name;

no rows selected

DBMON  @ lawrtdev> 
DBMON  @ lawrtdev> set echo off

Joel Patterson
Database Administrator
904 727-2546

--
http://www.freelists.org/webpage/oracle-l

Other related posts: