CREATE SYNONYM

The CREATE SYNONYM statement allows you to create an alternate name for a table or a view.

Syntax

CREATE SYNONYM( tableName } );

synonymName

An SQLIdentifier, which can optionally include a schema name. This is the new name you want to create for the view or table.

viewName

An SQLIdentifier that identifies the view for which you are creating a synonym.

tableName

An SQLIdentifier that identifies the table for which you are creating a synonym.

Usage

Currently, you can only use a synonym instead of the original qualified table or view name in these statements: DELETE.

Here are a few other important notes about using synonyms:

  • Synonyms share the same name space 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.
  • You can create a synonym for a table or view that does not yet exist; however, you can only use the synonym if the table or view is present in your database.
  • You can create synonyms for other synonyms (nested synonyms); however, an error will occur if you attempt to create a synonym that results in a circular reference.
  • You cannot create synonyms in system schemas. Any schema that starts with SYS is a system schema.
  • You cannot define a synonym for a temporary table.

Example

splice> CREATE SYNONYM Hitting FOR Batting;
0 rows inserted/updated/deleted

splice> SELECT ID, Games FROM Batting WHERE ID < 11;
ID    |GAMES
-------------
1     |150
2     |137
3     |100
4     |143
5     |149
6     |93
7     |133
8     |52
9     |115
10    |100

0 rows inserted/updated/deleted

splice> SELECT ID, Games FROM Hitting WHERE ID < 11;
ID    |GAMES
-------------
1     |150
2     |137
3     |100
4     |143
5     |149
6     |93
7     |133
8     |52
9     |115
10    |100

0 rows inserted/updated/deleted

See Also