User's Manual

184 Appendix B. PL/SQL Standards
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)
);
Example B-1. Example of Constraint Name
Note
• If you have to abbreviate the table name for one of the constraints, abbreviate it for all the con-
straints.
• 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 pri-
mary 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)
Example B-2. Primary Key Naming
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: