User's Manual

Appendix B. PL/SQL Standards 185
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 (<[e-mail]>) 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, mainte-
nance, 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 cre-
ation 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