RE: naming conventions (constraints and indexes)

Joel,

You might want to be carefull about using the "use index" clause to create a 
supporting index while creating the constraint.

Such an index will be regarded by Oracle as an implicit index.

This has as effect that the index will not be recreated when performing an 
export / import if there is another index (unique or nonunique) which can be 
used by Oracle to support the constraint.
Also when dropping the constraint the index supporting it will also be dropped 
by default (if it is an unique index).

I also found that when you don't name the constraint, but you do name the 
index, the index name will still be regarded as being system generated.


SQL> create table t (veld1 number(10));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t add constraint t_pk primary key (veld1) using index (create 
unique index t_i on t (veld1));

Table altered.

SQL> select index_name, index_type, uniqueness, generated from user_indexes 
where table_name = 'T';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES G
------------------------------ --------------------------- --------- -
T_I                            NORMAL                      UNIQUE    N

SQL> select constraint_name, constraint_type, index_name, generated from 
user_constraints where table_name = 'T';

CONSTRAINT_NAME                C INDEX_NAME                     GENERATED
------------------------------ - ------------------------------ --------------
T_PK                           P T_I                            USER NAME

SQL> set linesize 120
SQL> column table_owner format a15
SQL> column table_name format a15
SQL> column index_owner format a15
SQL> column index_name format a15
SQL> column implicit format a10
SQL> column unique_index format a10
SQL>
SQL> 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
  dba_objects   obj_t,
  dba_objects   obj_i,
  7    8    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  order by
  table_name, index_name
 16   17  ;

TABLE_OWNER     TABLE_NAME      INDEX_OWNER     INDEX_NAME      IMPLICIT   
UNIQUE_IND
--------------- --------------- --------------- --------------- ---------- 
----------
FDH             T               FDH             T_I             YES        YES


SQL> alter table t drop constraint t_pk;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

no rows selected

SQL> alter table t add primary key (veld1) using index (create unique index t_i 
on t (veld1));

Table altered.

SQL> select index_name, index_type, uniqueness, generated from user_indexes 
where table_name = 'T';

INDEX_NAME      INDEX_TYPE                  UNIQUENES GENERATED
--------------- --------------------------- --------- ----------
T_I             NORMAL                      UNIQUE    Y

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

TABLE_OWNER     TABLE_NAME      INDEX_OWNER     INDEX_NAME      IMPLICIT   
UNIQUE_IND
--------------- --------------- --------------- --------------- ---------- 
----------
FDH             T               FDH             T_I             YES        YES



Regards,

 
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: woensdag 9 november 2011 20:18
To: oracle-l@xxxxxxxxxxxxx
Subject: naming conventions (constraints and indexes)

Its an art and a science isn't it.   I like suffixes for some things, and 
prefixes for others.   I'm limiting my question to constraints and associated 
indexes and only the suffixes.   I use suffixes for indexes.   (Just to satisfy 
curiosity, I use prefixes for things like sequences S_, functions F_, package 
bodies P_ and packages, views V_, Procedures _R etc).

Constraints have suffixes such as _FK, _CK..., so _UK, and _PK.  I notice that 
it is easy to create a primary or unique constraint with an index of the same 
name even with the using index clause.   The result is an index with suffix 
_UK, and PK.

However, I was wondering what some opinions are about giving extra attention to 
(developers, modelers etc) to separate further these types.  So all constraints 
are 'K' and all indexes 'X'.   e.g.  ( FK, FX),  (PK, PX), (UK, UX).

An immediate result that I see is that FK would not need the FK_I , (or even a 
number for multiples, as it would be simply end in _FX.  (NAME_FK, NAME_FX).

The idea of further refining this is somewhat a result of what Tom Kyte says 
about metadata and indexes, and that metadata is good.

Examples concerning metadata:
A constraint is metadata, an index is the mechanism.
A foreign key can be defined to a unique constraint, but not to a unique index.

This of course takes some extra effort.   I think as things get bigger that it 
is worth it.   Any consensus out there?

Example:  (leaving out FK).

CREATE TABLE EXAMPLE_TABLE (
  PRIM_COL number constraint EXAMPLE_TABLE_PK primary key
    using index ( CREATE INDEX EXAMPLE_TABLE_PX ON
      EXAMPLE_TABLE(PRIM_COL)),
  UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE
    using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on
      EXAMPLE_TABLE(UNIQ_COL)),
  junk varchar2(10)
);

select table_name, constraint_name, constraint_type, index_name
      from user_constraints where table_name = 'EXAMPLE_TABLE';

TABLE_NAME                CONSTRAINT_NAME           C INDEX_NAME
------------------------- ------------------------- ------------------
EXAMPLE_TABLE             EXAMPLE_TABLE_PK          P EXAMPLE_TABLE_PX
EXAMPLE_TABLE             EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX

DBMON  @ COSDEV>  select table_name, index_name from user_indexes where 
table_name = 'EXAMPLE_TABL

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
EXAMPLE_TABLE                  EXAMPLE_TABLE_PX
EXAMPLE_TABLE                  EXAMPLE_TABLE_UNIQ_COL_UX


Joel Patterson
Database Administrator
904 727-2546



Joel Patterson
Database Administrator
904 727-2546



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


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


Other related posts: