B.4. Constraint Naming Standards

B.4.1. Abbreviations

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

B.4.2. 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.

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

	Note
	If you have to abbreviate the table name for one of the constraints, abbreviate it for all the constraints. If you are defining a multicolumn constraint, try to truncate the two column names evenly.

B.4.3. Naming primary keys

Naming primary keys might not have any obvious advantages. However, in Example B-2, the primary key helps make the SQL query clearer.

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)

Being able to see EXAMPLE_TOPICS_TOPIC_ID_PK in the trace helps us to know exactly which table Oracle is querying.

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 (<[email protected]>) 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.

B.4.4. Naming not null Constraints is Optional.

Red Hat Applications developers are undecided on whether or not to name not null constraints. If you want to name them, please do so and follow the above naming standard. Currently, naming not null constraints is not a requirement of WAF.

	Note
	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.

B.4.5. Upgrade Scripts

Data model upgrade scripts are a crucial part of database-based applications. Standards help to ensure that upgrade scripts behave correctly and consistently. This helps save time in development, maintenance, and support.

	Tip
	An upgrade script should be written anytime that a change is made to a component's sql creation script that results in a different schema and/or data set. The new upgrade script should be written so that, when applied to the previous creation script, it results in the same schema and data set.

Every component should have exactly zero or one upgrade script per release per supported database. However, an upgrade script may source other files.

Upgrade scripts go in an upgrade/ directory below the directory containing the corresponding creation script. The name of the file should be <component-name>-<old-version-name>-<new-version-name>.sql.

Example:

cms/sql/oracle-se/upgrade/cms-4.6.4-4.6.5.sql

Extending this process farther, there should be a single upgrade script for all of WAF per version. This upgrade script lives in kernel/sql/oracle-se/upgrade and is called core-platform-<old-version-name> -<new-version-name>.sql.

Similar to core-platform-create.sql, this script will not have its own SQL commands, but will simply source other files. Updating this script is the responsibility of each component developer.