Naming conventions for Oracle DDL

Every application should have a 3 letter SYNONYM. eg:

application ORDERPROCESSING has synonym ODP


Every table should have a 3 letter SYNONYM
eg:
table EMPLOYEE has synonym EMP


A technical PK ID should be called EMP_ID

and it should be declared PK as follows:
CONSTRAINT ODP_EMP_PK PRIMARY KEY (EMP_ID) ENABLE

that is: {$applicationSynonym}_{$tableSynonym}_PK



all the VARCHAR2 columns should be declared with the "CHAR" size:
VARCHAR2(30 CHAR)

all the TIME information should be represented as TIMESTAMP(6)

The foreign keys go like this:

alter table EMPLOYEE add constraint
ODP_EMP_LAN_FK
FOREIGN KEY (LAN_ID)
REFERENCES LANGUAGE(LAN_ID);

where LANGUAGE is the FK table and LAN its synonym.

and always explicitly create the index associated to the FK
CREATE INDEX ODP_EMP_LAN_FK_I ON EMPLOYEE(LAN_ID);

Unique constraints UK should be declared this way, and can be attached to an index:

CONSTRAINT
ODP_EMP_UK
UNIQUE (EMP_CODE, EMP_LAN)
USING INDEX
(
create unique index
ODP_EMP_UK_I
on EMPLOYEE(EMP_CODE, EMP_LAN)
)