Constraint Naming Standards

Abbreviations

We propose the following naming convention for all constraints, with the following abbreviations taken from Oracle Docs at http://oradoc.photo.net/ora81/DOC/server.815/a67779/ch4e.htm#8953. Note that we shortened all of the constraint abbreviations to two characters to save room.

Format of constraint name

<table name>_<column_name>_<constraint abbreviation>

In reality, this won't be possible because of the character limitation on names inside Oracle. When the name is too long, we will follow these steps in order:

1. Abbreviate the table name with the table's initials (for example users -> u and users_contact -> uc).

2. Truncate the column name until it fits.

If the constraint name is still too long, you should consider rewriting your entire data model :)

Notes:

• If you have to abbreviate the table name for one of the constraints, abbreviate it for all the constraints

• If you are defining a multi column constraint, try to truncate the two column names evenly

Example

create table example_topics (
   topic_id    integer
    constraint example_topics_topic_id_pk
    primary key
);

create table constraint_naming_example (
   example_id
           integer
           constraint cne_example_id_pk
           primary key,
   one_line_description	   
           varchar(100)
           constraint cne_one_line_desc_nn
           not null,
   body    clob,
       up_to_date_p
       char(1) default('t')
       constraint cne_up_to_date_p_check
       check(up_to_date_p in ('t','f')),
   topic_id
       constraint cne_topic_id_nn not null
       constraint cne_topic_id_fk references example_topics,
       -- Define table level constraint
       constraint cne_example_id_one_line_unq 
          unique(example_id, one_line_description)
);

Why it is good to name primary keys

Naming primary keys might not have any obvious advantages. However, here is an example where naming the primary key really helps (and this is by no means a rare case!).

SQL> set autotrace traceonly explain;

SQL> select * from constraint_naming_example, example_topics 
where constraint_naming_example.topic_id = example_topics.topic_id;

Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'CONSTRAINT_NAMING_EXAMPLE'
   3    1     INDEX (UNIQUE SCAN) OF 'EXAMPLE_TOPICS_TOPIC_ID_PK' (UNIQUE)

Isn't it nice to see EXAMPLE_TOPICS_TOPIC_ID_PK in the trace and know exactly which table oracle is using at each step?

Note that if we had not named the constraints, the execution plan would look like:

Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'CONSTRAINT_NAMING_EXAMPLE' 3 1 INDEX (UNIQUE SCAN) OF 'SYS_C00140971' (UNIQUE)

The SYS_C00140971 by itself provides no information as to which index is being used in this query, and more importantly, the name of this constraint will vary from database to database.

Mark Lindsey provided another good reason to name primary keys and unique constraints. Oracle creates an index for every primary key and unique constraint with the same name as the constraint. It is an unfortunate DBA who has to wrestle with storage management of tens of mysteriously-named indexes.

Naming not null constraints is optional.

ArsDigita is split on whether or not we should be naming not null constraints. If you want to name them, please do so and follow the above naming standard. But, naming not null constraints is not a requirement at ArsDigita.

Note that naming the not null constraints does not help immediately in error debugging (for example the error will say something like Cannot insert null value into column). We do recommend naming not null constraints to be consistent in our naming of all constraints.