FAQ
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

Search Discussions

  • Joel Patterson at Nov 9, 2011 at 7:22 pm
    Resent.

    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. Nothing for Tables).

    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
  • Thomas Day at Nov 9, 2011 at 9:32 pm
    I would say that having a consistent naming convention that the developers
    buy in to is essential. It's important for your ability to debug
    problems. On the other hand, if you use TOAD exclusively it might not be
    so much of an issue.
    I don't think that there's a best naming convention. If you make it too
    complex the developers will complain about all the typing that they have to
    do. Also, Oracle is limited in the length of it's object names. But when
    you have a naming convention, make sure that it is applied.
  • Stephane Faroult at Nov 9, 2011 at 10:02 pm
    Joel,
    Concerning constraint naming, I am currently experimenting with giving
    "user-friendlyl" names to constraints - for instance, instead of
    EXAMPLE_TABLE_UNIQ_COL_UK as in your example, naming the constraint
    "UNIQ_COL must be unique", or, instead of EXAMPLE_TABLE_FLAG_CK, "FLAG
    must be Y or N".
    The idea is to have a hint about what went wrong returned to the user,
    courtesy of the error message that usually gives the name of the
    constraint that was violated. Of course, if no "raw" error message is
    ever returned to the user, it's not so important. The user will just
    call support saying that a window displayed "database error" on his/her
    screen.

    Needless to say, the 30-character Oracle limit is a problem (better with
    a DBMS that takes identifiers that can be 64 or 128 character long), and
    the unicity of constraint names in the namespace requires extensive
    creativity if you have tons of tables with an ACTIVE flag that can be Y
    or N.

    But it some cases, it can improve the "user experience" (I love this one).

    HTH
    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/09/2011 08:21 PM, Joel.Patterson@crowley.com wrote:
    Resent.

    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. Nothing for Tables).

    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



    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 10, 2011 at 1:44 pm
    Thanks Stephane, I make up those names for this example. I was going for the suffix portion of the names. We already have naming conventions, but I have been letting the constraint name and index name be identical. But I am looking for feedback on changing the suffix (PK,PX) (UK,UX) (FK,FX) ....
    Joel Patterson
    Database Administrator
    904 727-2546
    From: Stephane Faroult
    Sent: Wednesday, November 09, 2011 5:03 PM
    To: oracle-l@freelists.org; Patterson, Joel
    Subject: Re: naming conventions (constraints and indexes)

    Joel,

    Concerning constraint naming, I am currently experimenting with giving "user-friendlyl" names to constraints - for instance, instead of EXAMPLE_TABLE_UNIQ_COL_UK as in your example, naming the constraint "UNIQ_COL must be unique", or, instead of EXAMPLE_TABLE_FLAG_CK, "FLAG must be Y or N".
    The idea is to have a hint about what went wrong returned to the user, courtesy of the error message that usually gives the name of the constraint that was violated. Of course, if no "raw" error message is ever returned to the user, it's not so important. The user will just call support saying that a window displayed "database error" on his/her screen.

    Needless to say, the 30-character Oracle limit is a problem (better with a DBMS that takes identifiers that can be 64 or 128 character long), and the unicity of constraint names in the namespace requires extensive creativity if you have tons of tables with an ACTIVE flag that can be Y or N.

    But it some cases, it can improve the "user experience" (I love this one).

    HTH
    --
    Stephane Faroult
    RoughSea Ltd<http://www.roughsea.com>
    Konagora<http://www.konagora.com>
    RoughSea Channel on Youtube<http://www.youtube.com/user/roughsealtd>


    On 11/09/2011 08:21 PM, Joel.Patterson@crowley.com wrote:

    Resent.



    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. Nothing for Tables).



    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








    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F (LABOR) at Nov 10, 2011 at 1:57 pm
    Joel,

    I name constraints and indexes like this:

    Constraint Index
    Name Name
    Table_PK TABLE_PK
    Table_FK01 Table_K01
    Table_FK02 Table_K02
    Table_FK03 Table_K03

    Additional Indexes are named Table_Knnn.
    Check Constraints are named Table_Cnn.

    This way I can see clearly that each FK is supported by an index.

    So I think you and I do something similar. An easy way to relate Constraints and Indexes.

    Tom
  • Chitale, Hemant Krishnarao at Nov 10, 2011 at 4:09 am
    I prefer to create the Index *before* defining the constraint.
    That way, the Index is independent of the constraint.
    So
    a) the Index is preserved when I drop the constraint
    b) the Index can be non-Unique
    c) for composite definitions, the columns need not be in the same "order" in the Index definition


    http://hemantoracledba.blogspot.com/2010/07/preserving-index-when-dropping.html
    and
    http://hemantoracledba.blogspot.com/2011/03/primary-key-and-index.html




    Hemant K Chitale


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Joel.Patterson@crowley.com
    Sent: Thursday, November 10, 2011 3:21 AM
    To: oracle-l@freelists.org
    Subject: RE: naming conventions (constraints and indexes)

    ....deleted ....

    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



    This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

    Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 10, 2011 at 2:10 pm
    Interesting, and I have a couple observations from these urls.

    -- Yes, the ALTER TABLE ... DROP CONSTRAINT does have a "KEEP INDEX" clause.

    -- Hemant gives an example of the index and constraint name being different, but that when you drop the constraint, the ORA-0001 returns the index name. However, I interpret the results where, when they are the same name, that the index name is still returned... since the constraint was dropped, why would it be the constraint name, (they're the same -- I say it is the index name still).

    I'm just not too sure how much it matters that the index be independent of the constraint. Indeed it seems like a good idea ... However, Freek points out that the using index clause has a side affect where if another index is present that can support the constraint, then oracle will not recreate the one defined in the using index clause during import. (kind of news to me though -- but it seems like almost a redundant index situation).

    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: Chitale, Hemant Krishnarao
    Sent: Wednesday, November 09, 2011 11:09 PM
    To: Patterson, Joel
    Cc: oracle-l@freelists.org
    Subject: RE: naming conventions (constraints and indexes)


    I prefer to create the Index *before* defining the constraint.
    That way, the Index is independent of the constraint.
    So
    a) the Index is preserved when I drop the constraint
    b) the Index can be non-Unique
    c) for composite definitions, the columns need not be in the same "order" in the Index definition


    http://hemantoracledba.blogspot.com/2010/07/preserving-index-when-dropping.html
    and
    http://hemantoracledba.blogspot.com/2011/03/primary-key-and-index.html




    Hemant K Chitale


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Joel.Patterson@crowley.com
    Sent: Thursday, November 10, 2011 3:21 AM
    To: oracle-l@freelists.org
    Subject: RE: naming conventions (constraints and indexes)

    ....deleted ....

    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



    This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

    Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l
  • D'Hooge Freek at Nov 9, 2011 at 11:11 pm
    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@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Joel.Patterson@crowley.com
    Sent: woensdag 9 november 2011 20:18
    To: oracle-l@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 @ 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
  • Joel Patterson at Nov 10, 2011 at 1:49 pm
    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
  • Joel Patterson at Nov 10, 2011 at 2:12 pm
    Duh, thx
    Joel Patterson
    Database Administrator
    904 727-2546
    From: Phillip Jones
    Sent: Thursday, November 10, 2011 8:55 AM
    To: Patterson, Joel
    Cc: ORACLE-L
    Subject: Re: naming conventions (constraints and indexes)

    Is the object owner really 'FDH' ?

    Looks like it's 'DBMON' if your sqlplus prompt is anything to go by.

    Cheers,

    Phil
    On Thu, Nov 10, 2011 at 1:46 PM, wrote:
    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?p0: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#<http://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#<http://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<tel:904%20727-2546>
  • Joel Patterson at Nov 10, 2011 at 3:03 pm
    I think I missed something.
    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.

    Both examples shows IMPLICIT
    Both examples use the using index clause.


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

    The First example has a USER generated name, T_I.
    The Second example has a SYSTEM generated name, SYS_C00112556. -- Clearly I do not want this!!!

    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.
    -- Both examples show IMPLICIT?
    Also when dropping the constraint the index supporting it will also be dropped by default (if it is an unique index).
    -- unless you use KEEP INDEX clause.









    I reran your queries and added another table creating indexes without UNIQUE modifier below (just to have a third example) for reference:


    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.


    create table t (veld1 number(10));

    Table created.


    insert into t values (1);

    1 row created.

    insert into example_table values (1,1,'Example');

    1 row created.

    commit;

    Commit complete.



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

    Table altered.


    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_PX NORMAL NONUNIQUE N
    EXAMPLE_TABLE_UNIQ_COL_UX NORMAL NONUNIQUE N
    T_I NORMAL UNIQUE N


    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


    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 = 'DBMON'
    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 ;

    TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME IMPLICIT UNIQUE_INDEX
    -------------------- -------------------- -------------------- ------------------------------ -------------------- --------------------
    DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_PX YES NO
    DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_UNIQ_COL_UX YES NO
    DBMON T DBMON T_I YES YES


    alter table t drop constraint t_pk;

    Table altered.


    select index_name from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');

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


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

    Table altered.


    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_PX NORMAL NONUNIQUE N
    EXAMPLE_TABLE_UNIQ_COL_UX NORMAL NONUNIQUE N
    T_I NORMAL UNIQUE Y


    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
    SYS_C00112556 P T_I GENERATED NAME


    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 = 'DBMON'
    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;

    TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME IMPLICIT UNIQUE_INDEX
    -------------------- -------------------- -------------------- ------------------------------ -------------------- --------------------
    DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_PX YES NO
    DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_UNIQ_COL_UX YES NO
    DBMON T DBMON T_I YES YES
  • D'Hooge Freek at Nov 11, 2011 at 7:54 am
    Joel,

    The first example was to show that Oracle is considering the index created in the "using index" clause as an implicit index.

    The second example was to show that if the constraint is not explicitly named, the name of the index created using the "using index" clause is considered as a system generated name, despite the fact that you did name it.

    Only an index that is created separately from the constraint creation is considered as an explicit index.
    Also when dropping the constraint the index supporting it will also be dropped by default (if it >>is an unique index).
    -- unless you use KEEP INDEX clause.
    True, that's why I said: "by default" and "(if it is an unique index)"
    It is actually not a bad idea to specify "keep index" or "drop index" every time to guarantee a consistent behaviour.


    Regards,


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
  • Herring Dave - dherri at Nov 10, 2011 at 2:30 pm
    We've used various unique suffixes to identify the different object types, similar to what you listed, but what I've found is problems with the base name. What happens when you have a long table name, such as ACCOUNT_TRANSACTION_HISTORY_TB. Ignore trying to come up with a better table name for now as the example is contrived. I was on a project in a previous company where they decided than any dependent objects would use a 4-character abbreviation off the table name, using the first initial of each "word" in the table name. If the table has only 1 word then use the first 4 characters of that word. If it has 2 words then use 2 characters of each word, etc.

    So in this example all objects based on ACCOUNT_TRANSACTION_HISTORY_TB would use ACTH to identify the object it's based on. Indexes would be named like "ACTH_<col abbrev>_PK", having a similar convention for columns involved. A PK on ACCOUNT_ID and ACCOUNT_DT would be named "ACTH_AI_AD_PK". We had rules to deal with conflicts, pretty much any issue, but I won't bore you all with those details.

    Incredibly cryptic? Yup, but we never had issues with running out of room for object names, followed the same logic for everything and were able to nearly include all important info on object names to help make those names have all key info. Eventually you get use to it and automatically decrypt without issue.

    Take it for just another example of what others have done. This example was from a HIGHLY normalized OLTP db. We also had (and maintained) conceptual, logical, and physical data models, which I haven't seen since on any project.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Joel.Patterson@crowley.com
    Sent: Wednesday, November 09, 2011 1:18 PM
    To: oracle-l@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 @ 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

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 10, 2011 at 3:38 pm
    I worked somewhere where they did something similar as well. Essentially what you have described is abbreviated names with rules.

    I let them use descriptive names for tables and columns with no suffixes or prefixes (it can get complicated with multiple apps if you've gone there).

    Our rules come in with the constraints, indexes. I let the developers create the names, but sometimes the time comes when a few get in and its time to clean up.

    So for the 30 character limit, One thing that can be done is, 'for instance', with Null constraints is this small script below. It will take up to 10 different tries to create a unique name using rules.

    I have variations of this for different reasons and tasks.

    WARNING: THIS SCRIPT WILL RENAME THE CONSTRAINTS WITHOUT ASKING AGAIN.

    -- RENAME CONSTRAINT SCRIPT.
    -- alter table physical_flights rename constraint abc to PF_CKC_FLIGHT_STAT;

    set serveroutput on size 1000000
    set feedback off
    set pagesize 200
    set linesize 2000
    set trim on
    set heading off
    set verify off
    set scan on
    set echo off

    column search_condition format a100
    column v_username new_value dsp_username
    column v_dbname new_value dsp_dbname

    set termout off
    select user v_username,
    decode(instr(global_name,'.'),0,global_name,substr(global_name,1,instr(global_name,'.')-1)) v_dbname
    from global_name;
    set termout on

    spool 1_rename_cons_nn_dynamic_&&dsp_username._&&dsp_dbname..lis
    declare

    -- *****************************************************************************
    -- * Script: 1_rename_all_cons_nn_dynamic.SQL *
    -- * Author: Joel patterson *
    -- * Usage: run it *
    -- * Function: rename not null constraints, *
    -- * This script immediately renames all not null constraints for *
    -- * the current user. By renaming immediate after the new constraint name has
    -- * been decided, allows for duplicates to be discarded in the exception
    -- * section. other wise the second or third duplication would have the same
    -- * Example: if three constraints would generate the name table_column_nn
    -- * and you spooled to a file, the constraint already exists error would
    -- * generate for the second and third. However in this script an exception
    -- * would catch the error and a new name would be generated.
    -- *****************************************************************************
    name_already_used EXCEPTION;
    pragma exception_init(name_already_used, -02264);
    t_txt varchar2(255);
    t_search varchar2(2000);
    l_cnt number;
    t_cons varchar2(30);
    finished boolean;
    t_tbl_nam varchar2(30);
    t_col_nam varchar2(30);
    isnullable char(1);

    cursor c_constraints is
    select c.CONSTRAINT_NAME CONSTRAINT_NAME,
    c.CONSTRAINT_TYPE CONSTRAINT_TYPE,
    c.STATUS STATUS,
    c.SEARCH_CONDITION SEARCH_CONDITION,
    c.table_name TABLE_NAME,
    cc.column_name COLUMN_NAME
    from user_CONSTRAINTS c, user_CONS_COLUMNS cc
    where c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
    and c.CONSTRAINT_TYPE = 'C'
    order by c.table_name, c.constraint_name;

    BEGIN

    FOR c_con in c_constraints LOOP
    BEGIN
    t_search := trim(replace(c_con.SEARCH_CONDITION,chr(10),' '));

    IF (instr(t_search,'IS NOT NULL') > 0) THEN
    --> NULL CONSTRAINTS
    -- If old constraint name is already the same as new composed name then leave alone.
    if (c_con.constraint_name = substr(c_con.table_name,1,10) ||'_'|| substr(c_con.column_name,1,14)||'_NN') THEN
    NULL;
    ELSE
    t_tbl_nam := c_con.table_name;
    t_col_nam := c_con.column_name;
    select nullable into isnullable
    from user_tab_columns
    where table_name = t_tbl_nam
    and column_name = t_col_nam;
    IF isnullable = 'N' THEN
    t_txt := 'ALTER TABLE "'||c_con.table_name||'" RENAME CONSTRAINT '||c_con.constraint_name||' to ';
    t_txt := t_txt || substr(c_con.table_name,1,10) ||'_'|| substr(c_con.column_name,1,14)||'_NN;';
    t_txt := substr(t_txt,1,length(t_txt)-1);
    execute immediate t_txt;
    dbms_output.put_line(t_txt||';');
    END IF;
    END IF;
    END IF;

    EXCEPTION
    WHEN name_already_used THEN
    dbms_output.put_line('rem EXCEPTION');
    finished := FALSE;

    -- if the new composed name already exists try and change it again up to 9 times.
    For i in 1..9 LOOP
    IF not finished THEN
    t_cons := substr(c_con.table_name,1,10-i) ||'_'|| substr(c_con.column_name,1,14+i)||'_NN';
    select count(*) into l_cnt from user_constraints where constraint_name = t_cons;
    IF l_cnt = 0 THEN
    t_txt := 'ALTER TABLE "'||c_con.table_name||'" RENAME CONSTRAINT '||c_con.constraint_name||' to ';
    t_txt := t_txt || t_cons;
    dbms_output.put_line(t_txt||';');
    execute immediate t_txt;
    finished := TRUE;
    END IF;
    END IF;
    END LOOP;

    END;
    END LOOP;
    END;
    /

    prompt prompt
    prompt prompt *************************************************************************
    prompt prompt OUTPUT spooled to 1_rename_cons_nn_dynamic_&&dsp_username._&&dsp_dbname..lis
    prompt prompt *************************************************************************
    prompt prompt

    spool off
    set feedback on
    set heading on
    undefine v_dbname
    undefine v_username
    undefine dsp_dbname
    undefine dsp_username


    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: Herring Dave - dherri
    Sent: Thursday, November 10, 2011 9:28 AM
    To: Patterson, Joel; oracle-l@freelists.org
    Subject: RE: naming conventions (constraints and indexes)

    We've used various unique suffixes to identify the different object types, similar to what you listed, but what I've found is problems with the base name. What happens when you have a long table name, such as ACCOUNT_TRANSACTION_HISTORY_TB. Ignore trying to come up with a better table name for now as the example is contrived. I was on a project in a previous company where they decided than any dependent objects would use a 4-character abbreviation off the table name, using the first initial of each "word" in the table name. If the table has only 1 word then use the first 4 characters of that word. If it has 2 words then use 2 characters of each word, etc.

    So in this example all objects based on ACCOUNT_TRANSACTION_HISTORY_TB would use ACTH to identify the object it's based on. Indexes would be named like "ACTH_<col abbrev>_PK", having a similar convention for columns involved. A PK on ACCOUNT_ID and ACCOUNT_DT would be named "ACTH_AI_AD_PK". We had rules to deal with conflicts, pretty much any issue, but I won't bore you all with those details.

    Incredibly cryptic? Yup, but we never had issues with running out of room for object names, followed the same logic for everything and were able to nearly include all important info on object names to help make those names have all key info. Eventually you get use to it and automatically decrypt without issue.

    Take it for just another example of what others have done. This example was from a HIGHLY normalized OLTP db. We also had (and maintained) conceptual, logical, and physical data models, which I haven't seen since on any project.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Joel.Patterson@crowley.com
    Sent: Wednesday, November 09, 2011 1:18 PM
    To: oracle-l@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 @ 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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 9, '11 at 7:19p
activeNov 11, '11 at 7:54a
posts15
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase