RE: naming conventions (constraints and indexes)
Date: Thu, 10 Nov 2011 00:12:30 +0100
Message-ID: <4814386347E41145AAE79139EAA398981939BEE31A_at_ws03-exch07.iconos.be>
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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: woensdag 9 november 2011 20:18
To: oracle-l_at_freelists.org
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 _at_ 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-lReceived on Wed Nov 09 2011 - 17:12:30 CST