Constraint naming standard

By Michael Bryzek

OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff.

The Big Picture

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

  • We can quickly identify and fix any errors.

  • We can reliability modify or drop constraints

Why do we need a naming convention?

Oracle limits names, in general, to 30 characters, which is hardly enough for a human readable constraint name.

Abbreviations

We propose the following naming convention for all constraints, with the following abbreviations taken from Oracle Docs. Note that we shortened all of the constraint abbreviations to two characters to save room.

Constraint typeAbbreviation
references (foreign key)fk
uniqueun
primary keypk
checkck
not nullnn
indexidx

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

  1. Abbreviate the table name with the table's initials (e.g. 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's good to name primary keys

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!

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?

Naming not null constraints is optional...

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.

About Naming the not null constraints

Though naming "not null" constraints doesn't help immediately 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.

($‌Id: constraint-naming.xml,v 1.10.2.1 2019/08/09 20:04:23 gustafn Exp $)