RE: naming conventions (constraints and indexes)
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Freek.DHooge@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2011 08:46:34 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
