Oracle : Database Object Naming Conventions

Each Object in a database has a name. Infact each index, view, constraint, synonym, and object in the database has its own unique name.

When you use the SQL keyword CREATE to create a database object, you must come up with a name and assign it to the object, and sometimes - as in the case of a table, to individual components within the object, such as the columns of a table.

The rules of naming objects are identical for all of these objects and object components. In other words, these rules apply to names for tables, table columns, views, constraints(if any are created), anything you must name in the database.

Index

1. Naming Rules Basics
2. Oracle Reserved Words, Keywords, and Namespaces
• Oracle Reserved Words
• Oracle Keywords
• PL/SQL Reserved Words
3. Case Sensitivity and Double Quotation marks
4. Unique names and namespaces
5. Naming Guidelines
6. System-Assigned Names

Naming Rules Basics

The rules for naming tables, and any database object, include the following:

• The length of the name must be at least one character, and no more than 30 characters.
• The first character in a name must be a letter.
• After the first letter, names may include letters, numbers, the dollar sign ($), the underscore (_), and the pound sign (#), also known as the hash mark or hash symbol. No other special characters are allowed anywhere in the name.
• Names cannot be reserved words that are set aside for use in SQL statements, such as the reserved words SELECT, CREATE, etc. See the following complete list of reserved words from Oracle’s SQL Language Reference Manual. These words are off limits when you create names for your database objects.

These rules are absolute. If you attempt to create a table or any other database object with a name that violates these rules, the attempt will fail, you’ll receive an error code from the database, and your object will not exist.

Oracle Reserved Words, Keywords, and Namespaces

1. Oracle Reserved Words

The following words are reserved by Oracle. That is, they have a special meaning to Oracle and so cannot be redefined. For this reason, you cannot use them to name database objects such as columns, tables, or indexes.


ACCESS	COMPRESS	FROM	LOCK	OPTION	SELECT	UPDATE
ADD	CONNECT	GRANT	LONG	OR	SESSION	USER
ALL	CREATE	GROUP	MAXEXTENTS	ORDER	SET	VALIDATE
ALTER	CURRENT	HAVING	MINUS	PCTFREE	SHARE	VALUES
AND	DATE	IDENTIFIED	MODE	PRIOR	SIZE	VARCHAR
ANY	DECIMAL	IMMEDIATE	MODIFY	PRIVILEGES	SMALLINT	VARCHAR2
ARRAYLEN	DEFAULT	IN	NOAUDIT	PUBLIC	SQLBUF	VIEW
AS	DELETE	INCREMENT	NOCOMPRESS	RAW	SUCCESSFUL	WHENEVER
ASC	DESC	INDEX	NOT	RENAME	SYNONYM	WHERE
AUDIT	DISTINCT	INITIAL	NOTFOUND	RESOURCE	SYSDATE	WITH
BETWEEN	DROP	INSERT	NOWAIT	REVOKE	TABLE
BY	ELSE	INTEGER	NULL	ROW	THEN
CHAR	EXCLUSIVE	INTERSECT	NUMBER	ROWID	TO
CHECK	EXISTS	INTO	OF	ROWLABEL	TRIGGER
CLUSTER	FILE	IS	OFFLINE	ROWNUM	UID
COLUMN	FLOAT	LEVEL	ON	ROWS	UNION
COMMENT	FOR	LIKE	ONLINE	START	UNIQUE




2. Oracle Keywords

The following words also have a special meaning to Oracle but are not reserved words and so can be redefined. However, some might eventually become reserved words.


ADMIN	COUNT	EVENTS	FUNCTION	SQLSTATE	ONLY
AFTER	PROFILE	EXCEPT	GO	STATEMENT_ID	OPEN
ALLOCATE	QUOTA	EXCEPTIONS	GOTO	STATISTICS	OPTIMAL
ANALYZE	READ	EXEC	GROUPS	STOP	OWN
ARCHIVE	REAL	EXPLAIN	INCLUDING	STORAGE	PACKAGE
ARCHIVELOG	RECOVER	EXECUTE	INDICATOR	SUM	PARALLEL
AUTHORIZATION	REFERENCES	EXTENT	INITRANS	SWITCH	PCTINCREASE
AVG	REFERENCING	EXTERNALLY	INSTANCE	SYSTEM	PCTUSED
BACKUP	RESETLOGS	FETCH	INT	TABLES	PLAN
BEGIN	RESTRICTED	FLUSH	KEY	TABLESPACE	PLI
BECOME	REUSE	FREELIST	LANGUAGE	TEMPORARY	PRECISION
BEFORE	ROLE	FREELISTS	LAYER	THREAD	PRIMARY
BLOCK	ROLES	FORCE	LINK	TIME	PRIVATE
BODY	ROLLBACK	FOREIGN	LISTS	MOUNT	PROCEDURE
CACHE	CURSOR	FORTRAN	LOGFILE	NEXT	TRACING
CANCEL	CYCLE	SAVEPOINT	MANAGE	NEW	TRANSACTION
CASCADE	DATABASE	SCHEMA	MANUAL	NOARCHIVELOG	TRIGGERS
CHANGE	DATAFILE	SCN	MAX	NOCACHE	TRUNCATE
CHARACTER	DBA	SECTION	MAXDATAFILES	NOCYCLE	UNDER
CHECKPOINT	DEC	SEGMENT	MAXINSTANCES	NOMAXVALUE	UNLIMITED
CLOSE	DECLARE	SEQUENCE	MAXLOGFILES	NOMINVALUE	UNTIL
COBOL	DISABLE	SHARED	MAXLOGHISTORY	NONE	USE
COMMIT	DISMOUNT	SNAPSHOT	MAXLOGMEMBERS	NOORDER	USING
COMPILE	DOUBLE	SOME	MAXTRANS	NORESETLOGS	WHEN
CONSTRAINT	DUMP	SORT	MAXVALUE	NORMAL	WRITE
CONSTRAINTS	EACH	SQL	MIN	NOSORT	WORK
CONTENTS	ENABLE	SQLCODE	MINEXTENTS	NUMERIC
CONTINUE	END	SQLERROR	MINVALUE	OFF
CONTROLFILE	ESCAPE	FOUND	MODULE	OLD




3. PL/SQL Reserved Words

The following PL/SQL keywords may require special treatment when used in embedded SQL statements.


ABORT	INSERT	COMPRESS	RECORD	ROWTYPE	FETCH
ACCEPT	INTEGER	CONNECT	CRASH	RUN	FLOAT
ACCESS	INTERSECT	CONSTANT	CREATE	SAVEPOINT	FOR
ADD	INTO	COUNT	CURRENT	SCHEMA	FORM
ALL	IS	NEW	CURRVAL	SELECT	FROM
ALTER	LEVEL	NEXTVAL	CURSOR	SEPARATE	SUM
AND	LIKE	NOCOMPRESS	DATABASE	SET	TABAUTH
ANY	LIMITED	NOT	DATA_BASE	SIZE	TABLE
ARRAY	LOOP	NULL	DATE	SMALLINT	TABLES
ARRAYLEN	MAX	NUMBER	DBA	SPACE	TASK
AS	MIN	NUMBER_BASE	DEBUGOFF	SQL	TERMINATE
ASC	MINUS	OF	DEBUGON	SQLCODE	THEN
ASSERT	MLSLABEL	ON	DECLARE	SQLERRM	TO
ASSIGN	MOD	OPEN	DECIMAL	START	TRUE
AT	MODE	OPTION	DEFAULT	STATEMENT	TYPE
AUTHORIZATION	NATURAL	OR	DEFINITION	STDDEV	UNION
AVG	BETWEEN	ORDER	DELAY	SUBTYPE	UNIQUE
BASE_TABLE	BINARY_INTEGER	OTHERS	DELETE	DIGITS	UPDATE
BEGIN	BODY	OUT	DELTA	DISPOSE	USE
FUNCTION	BOOLEAN	PACKAGE	DESC	DISTINCT	VALUES
GENERIC	BY	PARTITION	RELEASE	DO	VARCHAR
GOTO	CASE	PCTFREE	REMR	DROP	VARCHAR2
GRANT	CHAR	POSITIVE	RENAME	ELSE	VARIANCE
GROUP	CHAR_BASE	PRAGMA	RESOURCE	ELSIF	VIEW
HAVING	CHECK	PRIOR	RETURN	END	VIEWS
IDENTIFIED	CLOSE	PRIVATE	REVERSE	ENTRY	WHEN
IF	CLUSTER	PROCEDURE	REVOKE	EXCEPTION	WHERE
IN	CLUSTERS	PUBLIC	ROLLBACK	EXCEPTION_INIT	WHILE
INDEX	COLAUTH	RAISE	ROWID	EXISTS	WITH
INDEXES	COLUMNS	RANGE	ROWLABEL	EXIT	WORK
INDICATOR	COMMIT	REAL	ROWNUM	FALSE	XOR

Case Sensitivity and Double Quotation marks

The basic rule for case sensitivity with regard to naming database objects is that database objects are case insensitive and will be treated as though they are typed in uppercase letters. This is generally true, but there is an exception, and it depends on whether you use double quotation marks when you create an object. Here are the rules:

• If a name is not enclosed in double quotation marks when it is created, then it will be treated as uppercase regardless of how it is created or referenced.
• If a name is enclosed in double quotation marks, then it is case sensitive and must always be referenced with case sensitivity and with double quotation marks.
• By using double quotation marks, you can also include special characters that are otherwise not allowed, such as spaces and others. For example, this will work:

CREATE TABLE "Company Employees"
(employee_id NUMBER,
name VARCHAR2(35));

But remember that to refer to the table later, you’ll still need those double quotation marks, like this:

SELECT * FROM "Company Employees";

In other words, by using double quotation marks, you can specify database object names that go beyond the standard naming rules of Oracle objects. But if you do this, you will always need to use double quotation marks in every future reference to that object, and you’ll have to be specific with regard to the case of your object name.

Unique names and namespaces

What happens if you try to create a database object with a name that matches the name of another database object that’s already in the database? The answer is that it depends on your object’s relationship to that other object that already exists, and also to something called the “namespace”.

The namespace is a logical boundary within the database that encompasses a particular set of database objects. There are actually several namespaces at work at any given time, depending on the context in which you are working.

Understanding the namespace is necessary in order to understand whether you may or may not specify duplicate names for any particular database object. See below a diagram that demonstrates the namespace boundaries.

Fig 1.1 Oracle Database Object Naming Conventions - Namespace Boundries

Note that each square encloses a different namespace. In above figure, there are several namespaces identified:

1. USERS, ROLES, PUBLIC DB LINKS, TABLESPACES, PROFILES, PUBLIC SYNONYMS, etc objects are in their own collective namespace.
2. TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, STAND-ALONE PROCEDURES, STAND-ALONE STORED FUNCTIONS, PACKAGES, MATERIALIZED VIEWS, USER-DEFINED TYPES, TABLE PARTITIONS, etc objects share the same namespace within a given schema.
3. INDEX, CONSTRAINT, CLUSTERS, DATABASE TRIGGERS, PRIVATE DATABASE LINKS, DIMENSIONS, PACKAGE BODIES, LOBS, etc objects have their own namespace within a given schema.

What all of this means is that you must provide unique names for an object within its own namespace. Objects that share a namespace must have unique names within that namespace. Objects in different namespaces are allowed to have identical names.

Naming Guidelines

When naming objects, choose descriptive names that can be pronounced. Be consistent: if your tables of EMPLOYEES, CUSTOMERS, and VENDORS each include a reference to a person’s name, make those column names all the same—NAME, LAST_NAME and FIRST_NAME, whatever—just be consistent. Consider using a standard prefix for every database object.

that’s associated with a particular application—for example, for a Human Resources application, prefix each table with “HR_”—but avoid using prefixes that Oracle Corporation uses for its system-defined objects: “SYS_”, “ALL_”, “DBA_”, “GV$”, “NLS_”, “ROLE_”, “USER_”, and “V$”.

System-Assigned Names

Sometimes it happens that you may create an object indirectly. For example, when you create a table, and within the CREATE TABLE statement you optionally define an associated constraint, but without providing a name for the CONSTRAINT. The language syntax of the CREATE TABLE statement allows this to happen, and the result is not only your newly created named table, but also a newly created constraint for that table. Some developers refer to these constraints as “anonymous”, but they aren’t anonymous at all, the system will automatically generate a name for that constraint, a name that adheres to all the rules that we just reviewed.

However, system-defined names probably won’t adhere to the naming guidelines we just reviewed. They’ll adhere to the rules, yes. But they most assuredly will not adhere to the guidelines. Those guidelines are recommendations that Oracle makes to you, the developer. And as you can probably tell if you reflect on it a little, it won’t be possible for the system to automatically generate a name that is, for example, pronounceable and meaningful to your application. Therefore it’s good design to avoid the indirect creation of automatically generated names wherever possible. That’s a roundabout way of saying: be sure to name all of the database objects you create, including CONSTRAINTS, INDEXES, and others.

However, in order to make sure you name everything, you’ll need to know when and how all objects are created, directly and indirectly.

Related Blogs-

1. Oracle Database Introduction
2. Sql Blogs Listing
3. Oracle Blogs Listing

References-

1. https://docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm#CJHIIICD
2. OCA Oracle Database SQL Certified Expert Exam Guide by Steve O'Hearn

Subscribe to Java 4 Brains by Email