RE: naming conventions (constraints and indexes)
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Dave.Herring@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2011 10:36:57 -0500
I worked somewhere where they did something similar as well. Essentially what
you have described is abbreviated names with rules.
I let them use descriptive names for tables and columns with no suffixes or
prefixes (it can get complicated with multiple apps if you've gone there).
Our rules come in with the constraints, indexes. I let the developers create
the names, but sometimes the time comes when a few get in and its time to clean
up.
So for the 30 character limit, One thing that can be done is, 'for instance',
with Null constraints is this small script below. It will take up to 10
different tries to create a unique name using rules.
I have variations of this for different reasons and tasks.
WARNING: THIS SCRIPT WILL RENAME THE CONSTRAINTS WITHOUT ASKING AGAIN.
-- RENAME CONSTRAINT SCRIPT.
-- alter table physical_flights rename constraint abc to PF_CKC_FLIGHT_STAT;
set serveroutput on size 1000000
set feedback off
set pagesize 200
set linesize 2000
set trim on
set heading off
set verify off
set scan on
set echo off
column search_condition format a100
column v_username new_value dsp_username
column v_dbname new_value dsp_dbname
set termout off
select user v_username,
decode(instr(global_name,'.'),0,global_name,substr(global_name,1,instr(global_name,'.')-1))
v_dbname
from global_name;
set termout on
spool 1_rename_cons_nn_dynamic_&&dsp_username._&&dsp_dbname..lis
declare
-- *****************************************************************************
-- * Script: 1_rename_all_cons_nn_dynamic.SQL *
-- * Author: Joel patterson *
-- * Usage: run it *
-- * Function: rename not null constraints, *
-- * This script immediately renames all not null constraints for *
-- * the current user. By renaming immediate after the new constraint name has
-- * been decided, allows for duplicates to be discarded in the exception
-- * section. other wise the second or third duplication would have the same
-- * Example: if three constraints would generate the name table_column_nn
-- * and you spooled to a file, the constraint already exists error would
-- * generate for the second and third. However in this script an exception
-- * would catch the error and a new name would be generated.
-- *****************************************************************************
name_already_used EXCEPTION;
pragma exception_init(name_already_used, -02264);
t_txt varchar2(255);
t_search varchar2(2000);
l_cnt number;
t_cons varchar2(30);
finished boolean;
t_tbl_nam varchar2(30);
t_col_nam varchar2(30);
isnullable char(1);
cursor c_constraints is
select c.CONSTRAINT_NAME CONSTRAINT_NAME,
c.CONSTRAINT_TYPE CONSTRAINT_TYPE,
c.STATUS STATUS,
c.SEARCH_CONDITION SEARCH_CONDITION,
c.table_name TABLE_NAME,
cc.column_name COLUMN_NAME
from user_CONSTRAINTS c, user_CONS_COLUMNS cc
where c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and c.CONSTRAINT_TYPE = 'C'
order by c.table_name, c.constraint_name;
BEGIN
FOR c_con in c_constraints LOOP
BEGIN
t_search := trim(replace(c_con.SEARCH_CONDITION,chr(10),' '));
IF (instr(t_search,'IS NOT NULL') > 0) THEN
--> NULL CONSTRAINTS
-- If old constraint name is already the same as new composed name then
leave alone.
if (c_con.constraint_name = substr(c_con.table_name,1,10) ||'_'||
substr(c_con.column_name,1,14)||'_NN') THEN
NULL;
ELSE
t_tbl_nam := c_con.table_name;
t_col_nam := c_con.column_name;
select nullable into isnullable
from user_tab_columns
where table_name = t_tbl_nam
and column_name = t_col_nam;
IF isnullable = 'N' THEN
t_txt := 'ALTER TABLE "'||c_con.table_name||'" RENAME CONSTRAINT
'||c_con.constraint_name||' to ';
t_txt := t_txt || substr(c_con.table_name,1,10) ||'_'||
substr(c_con.column_name,1,14)||'_NN;';
t_txt := substr(t_txt,1,length(t_txt)-1);
execute immediate t_txt;
dbms_output.put_line(t_txt||';');
END IF;
END IF;
END IF;
EXCEPTION
WHEN name_already_used THEN
dbms_output.put_line('rem EXCEPTION');
finished := FALSE;
-- if the new composed name already exists try and change it again up
to 9 times.
For i in 1..9 LOOP
IF not finished THEN
t_cons := substr(c_con.table_name,1,10-i) ||'_'||
substr(c_con.column_name,1,14+i)||'_NN';
select count(*) into l_cnt from user_constraints where
constraint_name = t_cons;
IF l_cnt = 0 THEN
t_txt := 'ALTER TABLE "'||c_con.table_name||'" RENAME CONSTRAINT
'||c_con.constraint_name||' to ';
t_txt := t_txt || t_cons;
dbms_output.put_line(t_txt||';');
execute immediate t_txt;
finished := TRUE;
END IF;
END IF;
END LOOP;
END;
END LOOP;
END;
/
prompt prompt
prompt prompt
*************************************************************************
prompt prompt OUTPUT spooled to
1_rename_cons_nn_dynamic_&&dsp_username._&&dsp_dbname..lis
prompt prompt
*************************************************************************
prompt prompt
spool off
set feedback on
set heading on
undefine v_dbname
undefine v_username
undefine dsp_dbname
undefine dsp_username
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: Herring Dave - dherri [mailto:Dave.Herring@xxxxxxxxxx]
Sent: Thursday, November 10, 2011 9:28 AM
To: Patterson, Joel; oracle-l@xxxxxxxxxxxxx
Subject: RE: naming conventions (constraints and indexes)
We've used various unique suffixes to identify the different object types,
similar to what you listed, but what I've found is problems with the base name.
What happens when you have a long table name, such as
ACCOUNT_TRANSACTION_HISTORY_TB. Ignore trying to come up with a better table
name for now as the example is contrived. I was on a project in a previous
company where they decided than any dependent objects would use a 4-character
abbreviation off the table name, using the first initial of each "word" in the
table name. If the table has only 1 word then use the first 4 characters of
that word. If it has 2 words then use 2 characters of each word, etc.
So in this example all objects based on ACCOUNT_TRANSACTION_HISTORY_TB would
use ACTH to identify the object it's based on. Indexes would be named like
"ACTH_<col abbrev>_PK", having a similar convention for columns involved. A PK
on ACCOUNT_ID and ACCOUNT_DT would be named "ACTH_AI_AD_PK". We had rules to
deal with conflicts, pretty much any issue, but I won't bore you all with those
details.
Incredibly cryptic? Yup, but we never had issues with running out of room for
object names, followed the same logic for everything and were able to nearly
include all important info on object names to help make those names have all
key info. Eventually you get use to it and automatically decrypt without issue.
Take it for just another example of what others have done. This example was
from a HIGHLY normalized OLTP db. We also had (and maintained) conceptual,
logical, and physical data models, which I haven't seen since on any project.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring@xxxxxxxxxx
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM
The information contained in this communication is confidential, is intended
only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this communication
is strictly prohibited. If you have received this communication in error,
please resend this communication to the sender and delete the original message
or any copy of it from your computer system. Thank you.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Wednesday, November 09, 2011 1:18 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: naming conventions (constraints and indexes)
Its an art and a science isn't it. I like suffixes for some things, and
prefixes for others. I'm limiting my question to constraints and associated
indexes and only the suffixes. I use suffixes for indexes. (Just to satisfy
curiosity, I use prefixes for things like sequences S_, functions F_, package
bodies P_ and packages, views V_, Procedures _R etc).
Constraints have suffixes such as _FK, _CK..., so _UK, and _PK. I notice that
it is easy to create a primary or unique constraint with an index of the same
name even with the using index clause. The result is an index with suffix
_UK, and PK.
However, I was wondering what some opinions are about giving extra attention to
(developers, modelers etc) to separate further these types. So all constraints
are 'K' and all indexes 'X'. e.g. ( FK, FX), (PK, PX), (UK, UX).
An immediate result that I see is that FK would not need the FK_I , (or even a
number for multiples, as it would be simply end in _FX. (NAME_FK, NAME_FX).
The idea of further refining this is somewhat a result of what Tom Kyte says
about metadata and indexes, and that metadata is good.
Examples concerning metadata:
A constraint is metadata, an index is the mechanism.
A foreign key can be defined to a unique constraint, but not to a unique index.
This of course takes some extra effort. I think as things get bigger that it
is worth it. Any consensus out there?
Example: (leaving out FK).
CREATE TABLE EXAMPLE_TABLE (
PRIM_COL number constraint EXAMPLE_TABLE_PK primary key
using index ( CREATE INDEX EXAMPLE_TABLE_PX ON
EXAMPLE_TABLE(PRIM_COL)),
UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE
using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on
EXAMPLE_TABLE(UNIQ_COL)),
junk varchar2(10)
);
select table_name, constraint_name, constraint_type, index_name
from user_constraints where table_name = 'EXAMPLE_TABLE';
TABLE_NAME CONSTRAINT_NAME C INDEX_NAME
------------------------- ------------------------- ------------------
EXAMPLE_TABLE EXAMPLE_TABLE_PK P EXAMPLE_TABLE_PX
EXAMPLE_TABLE EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX
DBMON @ COSDEV> select table_name, index_name from user_indexes where
table_name = 'EXAMPLE_TABL
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
EXAMPLE_TABLE EXAMPLE_TABLE_PX
EXAMPLE_TABLE EXAMPLE_TABLE_UNIQ_COL_UX
Joel Patterson
Database Administrator
904 727-2546
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
