3.1 Synonyms

Table of Contents Previous Next


3 Enhanced SQL Features : 3.1 Synonyms

A synonym is an identifier that can be used to reference another database object in a SQL statement. A synonym is useful in cases where a database object would normally require full qualification by schema name to be properly referenced in a SQL statement. A synonym defined for that object simplifies the reference to a single, unqualified name.
Use the CREATE SYNONYM command to create a synonym. The syntax is:
syn_name is the name of the synonym. A synonym name must be unique within a schema.
schema specifies the name of the schema that the synonym resides in. If you do not specify a schema name, the synonym is created in the first existing schema in your search path.
object_name specifies the name of the object.
object_schema specifies the name of the schema that the object resides in.
Include the REPLACE clause to replace an existing synonym definition with a new synonym definition.
Include the PUBLIC clause to create the synonym in the public schema. The CREATE PUBLIC SYNONYM command creates a synonym that resides in the public schema:
The following example creates a synonym named personnel that refers to the enterprisedb.emp table.
Unless the synonym is schema qualified in the CREATE SYNONYM command, it will be created in the first existing schema in your search path. You can view your search path by executing the following command:
In our example, if a schema named development does not exist, the synonym will be created in the schema named accounting.
Now, the emp table in the enterprisedb schema can be referenced in any SQL statement (DDL or DML), by using the synonym, personnel:
To delete a synonym, use the command, DROP SYNONYM. The syntax is:
syn_name is the name of the synonym. A synonym name must be unique within a schema.
schema specifies the name of the schema in which the synonym resides.
You can optionally include the PUBLIC clause to drop a synonym that resides in the public schema. The DROP PUBLIC SYNONYM command drops a synonym that resides in the public schema:

3 Enhanced SQL Features : 3.1 Synonyms

Table of Contents Previous Next