RE: naming conventions (constraints and indexes)

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 10 Nov 2011 08:46:34 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8A2E2378_at_JAXMSG01.crowley.com>

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 _at_ 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 _at_ lawrtdev>
DBMON _at_ lawrtdev> create table t (veld1 number(10));

Table created.

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

1 row created.

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

1 row created.

DBMON _at_ lawrtdev> commit;

Commit complete.

DBMON _at_ 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 _at_ lawrtdev>
DBMON _at_ 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 _at_ lawrtdev>
DBMON _at_ 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  _at_ lawrtdev> 
DBMON  _at_ lawrtdev> 
DBMON  _at_ 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 _at_ lawrtdev>
DBMON _at_ lawrtdev> alter table t drop constraint t_pk;

Table altered.

DBMON _at_ lawrtdev>
DBMON _at_ 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 _at_ lawrtdev>
DBMON _at_ lawrtdev> alter table t add primary key (veld1)   2 using index (create unique index t_i on t (veld1));

Table altered.

DBMON _at_ lawrtdev>
DBMON _at_ 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 _at_ lawrtdev>
DBMON _at_ 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 _at_ lawrtdev>
DBMON _at_ lawrtdev> set echo off

Joel Patterson
Database Administrator
904 727-2546

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

Received on Thu Nov 10 2011 - 07:46:34 CST