Specifying Unique SQL Names

Using the Interactive SQL Service Facility › Specifying Unique SQL Names

Specifying Unique SQL Names

SQL names for schemas, tables, indexes, views, and synonyms must be unique according to the rules in the following table.

The SQL Statement	Defines In CA Datacom Datadictionary	SQL Name Requirements
CREATE SCHEMA	An AUTHORIZATION occurrence	The SQL name and the AUTHORIZATION occurrence name are the same and must be unique for all schemas.
CREATE TABLE	A TABLE occurrence	The SQL name of the table must be unique for all indexes, views and synonyms owned by a specific schema (authorization ID).
CREATE INDEX	A KEY occurrence	The SQL name of the index must be unique for all indexes owned by a specific schema (authorization ID).
CREATE VIEW	A VIEW occurrence	The SQL name of the view must be unique for all tables, views and synonyms owned by a specific schema (authorization ID).
CREATE SYNONYM	A SYNONYM occurrence	The SQL name of the synonym must be unique for all tables, views and synonyms owned by a specific schema (authorization ID).

See the chapter on the SQL transport utility (DDTRSLM) in the CA Datacom Datadictionary Batch Reference Guide for information about additional restrictions on words used for an AUTHID, SQL name, or CA Datacom Datadictionary occurrence name.

When you create SQL tables, views and synonyms, the SQL name is prefixed by the authorization ID to create the TABLE, VIEW and SYNONYM occurrence name. The format is authid-sqlname.

Together, the authorization ID and SQL name of each table, view and synonym must be unique within the schema. For example, the names JONES.DEPTTBL (for a table) and JONES.DEPTTBL (for a view) are not unique since both are owned by the JONES schema, but the names JONES.DEPTTBL (for a table) and SMITH.DEPTTBL (for a view) are unique because they are owned by different schemas.