CREATE SYNONYM statement

Use the CREATE SYNONYM statement to provide an alternate name for a table or a view that is present in the same schema or another schema. You can also create synonyms for other synonyms, resulting in nested synonyms. A synonym can be used instead of the original qualified table or view name in SELECT, INSERT, UPDATE, DELETE or LOCK TABLE statements. You can create a synonym for a table or a view that doesn't exist, but the target table or view must be present before the synonym can be used.

Synonyms share the same namespace as tables or views. You cannot create a synonym with the same name as a table that already exists in the same schema. Similarly, you cannot create a table or view with a name that matches a synonym already present.

A synonym can be defined for a table/view that does not exist when you create the synonym. If the table or view doesn't exist, you will receive a warning message (SQLSTATE 01522). The referenced object must be present when you use a synonym in a DML statement.

You can create a nested synonym (a synonym for another synonym), but any attempt to create a synonym that results in a circular reference will return an error message (SQLSTATE 42916).

Synonyms cannot be defined in system schemas. All schemas starting with 'SYS' are considered system schemas and are reserved by Derby.

A synonym cannot be defined on a temporary table. Attempting to define a synonym on a temporary table will return an error message (SQLSTATE XCL51).

Syntax

CREATE SYNONYM synonym-Name FOR { view-Name | table-Name }
The synonym-Name in the statement represents the synonym name you are giving the target table or view, while the view-Name or table-Name represents the original name of the target table or view.

Example

CREATE SYNONYM SAMP.T1 FOR SAMP.TABLEWITHLONGNAME
Related reference
CREATE FUNCTION statement
CREATE INDEX statement
CREATE PROCEDURE statement
CREATE ROLE statement
CREATE SCHEMA statement
CREATE SEQUENCE statement
CREATE TABLE statement
CREATE TRIGGER statement
CREATE TYPE statement
CREATE VIEW statement