<?xml version='1.0' ?>
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.4//EN"
               "http://www.oasis-open.org/docbook/xml/4.4/docbookx.dtd" [
<!ENTITY % myvars SYSTEM "../variables.ent">
%myvars;
]>
 <sect1 id="eng-standards-constraint-naming" xreflabel="Constraint naming standard">
<title>Constraint naming standard</title>

 
<authorblurb>
<para>By Michael Bryzek</para>
</authorblurb>

<sect2 id="eng-standards-constraint-naming-big-picture">
<title>The Big Picture</title>

<para>
Constraint naming standard is important for one reason: The SYS_* name oracle
assigns to unnamed constraints is not very understandable. By correctly
naming all contraints, we can quickly associate a particular constraint 
with our data model. This gives us two real advantages:
</para>

<itemizedlist>
  <listitem><para> We can quickly identify and fix any errors. </para></listitem>
  <listitem><para> We can reliabily modify or drop constraints </para></listitem>
</itemizedlist>

<para>
<phrase>Why do we need a naming convention? </phrase>
<ulink url="http://oradoc.photo.net/ora8doc/DOC/server803/A54647_01/ch2.htm#2956">Oracle limits names</ulink>, 
in general, to 30 characters, which is hardly enough for a human readable constraint name.
</para>

</sect2>

<sect2 id="eng-standards-constraint-naming-abbr">
<title>Abbreviations</title>

<para>
We propose the following naming convention for all constraints, with
the following abbreviations taken from Oracle Docs at 
<ulink url="http://oradoc.photo.net/ora81/DOC/server.815/a67779/ch4e.htm#8953">
http://oradoc.photo.net/ora81/DOC/server.815/a67779/ch4e.htm#8953</ulink>. 
Note that we shortened all of the constraint abbrevations to 
two characters to save room.
</para>
 
<informaltable>
<tgroup cols="2">
<thead>
<row>
  <entry>Constraint type</entry>
  <entry>Abbreviation</entry>
</row>
</thead>
<tbody>
<row>
  <entry>references (foreign key)</entry>
  <entry>fk</entry>
</row>
<row>
  <entry>unique</entry>
  <entry>un</entry>
</row>
<row>
  <entry>primary key</entry>
  <entry>pk</entry>
</row>
<row>
  <entry>check</entry>
  <entry>ck</entry>
</row>
<row>
  <entry>not null</entry>
  <entry>nn</entry>
</row>
</tbody></tgroup></informaltable>


</sect2>

<sect2 id="eng-standards-constraint-naming-format">
<title>Format of constraint name</title>

<para>
&lt;table name&gt;_&lt;column_name&gt;_&lt;constraint abbreviation&gt;
</para>

<para>
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 two
steps in order:
</para>

<orderedlist>
  <listitem><para> Abbreviate the table name with the table's initials (e.g. users -&gt; u and users_contact -&gt; uc).
</para></listitem>
  <listitem><para> Truncate the column name until it fits.</para></listitem>
</orderedlist>

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

<para><emphasis role="strong">Notes:</emphasis></para>

<itemizedlist>
  <listitem><para> If you have to abbreviate the table name for one of the constraints, abbreviate it for all the constraints</para></listitem>
  <listitem><para> If you are defining a multi column constraint, try to truncate the two column names evenly </para></listitem>
</itemizedlist>

</sect2>

<sect2 id="eng-standards-constraint-naming-example">
<title>Example</title>


<programlisting>
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)
);

</programlisting>

</sect2>

<sect2 id="eng-standards-constraint-naming-pk">
<title>Why it's good to name primary keys</title>

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

<programlisting>
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' (UNI
	  QUE)
</programlisting>

<para>
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?
</para>

</sect2>

<sect2 id="eng-standards-constraint-naming-nn">
<title>Naming not null constraints is optional...</title>

<para>
People disagree on whether or not we should be naming not null
constraints.  So, if you want to name them, please do so and follow
the above naming standard. But, naming not null constraints is not a
requirement.
</para>

<para>
<phrase>About Naming the not null constraints</phrase>
</para>
<para>
Though naming "not null" constraints doesn't help immeditately in error
debugging (e.g. the error will say something like 
"Cannot insert null value into column"), we recommend naming not null
constraints to be consistent in our naming of all constraints.
</para>

<para><phrase role="cvstag">($Id: constraint-naming.xml,v 1.6 2006/07/17 05:38:37 torbenb Exp $)</phrase></para>

</sect2>

</sect1>
