CREATE SYNONYM statement
Use the CREATE SYNONYM statement to declare and register an alternative name for an existing table, view, or sequence object.
This statement is an extension to the ANSI/ISO standard for SQL.
Syntax
- 1 This keyword is valid only in databases that are not MODE ANSI
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| sequence | Name of a local sequence | Must exist in the current database | Identifier |
| table, view | Name of database table, external table, or view for which synonym is being created | Must be registered in the current database, or in a database specified in a qualifier | Database Object Name |
| synonym | Synonym declared here for the name of table, view, or sequence | Must be unique among table object names in the database; see also
Usage notes. Note: In a non-ANSI database, a synonym is PUBLIC by default, but in a
MODE ANSI database, it is private and cannot be made public. |
Database Object Name |
Usage
Users have the same privileges for a synonym that they have for the database object that the synonym references. The syssyntable and systables system catalog tables maintain information about synonyms.
You cannot create a synonym for a synonym in the same database.
The identifier of the synonym must be unique among the names of tables, temporary tables, external tables, views, and sequence objects in the same database. (See, however, the section Synonyms with the Same Name.)
If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a synonym of the specified name is already registered in the current database, or if the specified name is the identifier of a table, view, or sequence object in the current database.
Once a synonym is created, it persists until the owner executes the DROP SYNONYM statement. (This persistence distinguishes a synonym from an alias that you can declare in the FROM clause of a SELECT statement. The alias is in scope only while that SELECT statement is executing.)
If a synonym refers to a table, view, or sequence in the same database, however, the synonym is automatically dropped if the referenced table, view, or sequence object is dropped. For additional information, see the section Synonyms for objects outside the current database.