Informix Guide to SQL: Syntax SQL Statements

Use the CREATE SYNONYM statement to provide an alternative name for a table or view.

Users have the same privileges for a synonym that they have for the table to which the synonym applies.

The synonym name must be unique; that is, the synonym name cannot be the same as another database object, such as a table, view, or temporary table.

Once a synonym is created, it persists until the owner executes the DROP SYNONYM statement. This property distinguishes a synonym from an alias that you can use in the FROM clause of a SELECT statement. The alias persists for the existence of the SELECT statement. If a synonym refers to a table or view in the same database, the synonym is automatically dropped if you drop the referenced table or view.

You cannot create a synonym for a synonym in the same database.

In an ANSI-compliant database, the owner of the synonym (owner.synonym) qualifies the name of a synonym. The identifier owner.synonym must be unique among all the synonyms, tables, temporary tables, and views in the database. You must specify owner when you refer to a synonym that another user owns. The following example shows this convention:

CREATE  SYNONYM  emp  FOR  accting.employee 

You can create a synonym for any table or view in any database on your database server. Use the owner. convention if the table is part of an ANSI-compliant database. The following example shows a synonym for a table outside the current database. It assumes that you are working on the same database server that contains the payables database.

CREATE  SYNONYM  mysum  FOR  payables:jean.summary 

You can create a synonym for a table or view that exists on any networked database server as well as on the database server that contains your current database. The database server that holds the table must be on-line when you create the synonym. In a network, the database server verifies that the database object referred to by the synonym exists when you create the synonym.

The following example shows how to create a synonym for a database object that is not in the current database:

CREATE  SYNONYM  mysum  FOR  payables@phoenix:jean.summary 

The identifier mysum now refers to the table jean.summary, which is in the payables database on the phoenix database server. Note that if the summary table is dropped from the payables database, the mysum synonym is left intact. Subsequent attempts to use mysum return the error Table not found.

You cannot create synonyms on the following types of remote tables:

• Typed tables (including any table that is part of a table hierarchy)
• Tables that contain any extended data types

If you use the PUBLIC keyword (or no keyword at all), anyone who has access to the database can use your synonym. If a synonym is public, a user does not need to know the name of the owner of the synonym. Any synonym in a database that is not ANSI compliant and was created in an Informix database server earlier than Version 5.0 is a public synonym.

In an ANSI-compliant database, synonyms are always private. If you use the PUBLIC or PRIVATE keywords, you receive a syntax error.

If you use the PRIVATE keyword, the synonym can be used only by the owner of the synonym or if the name of the owner is specified explicitly with the synonym. More than one private synonym with the same name can exist in the same database. However, a different user must own each synonym with that name.

You can own only one synonym with a given name; you cannot create both private and public synonyms with the same name. For example, the following code generates an error:

CREATE SYNONYM our_custs FOR customer;

CREATE PRIVATE SYNONYM our_custs FOR cust_calls;-- ERROR!!!

If you own a private synonym, and a public synonym exists with the same name, when you use the synonym by its unqualified name, the private synonym is used.

If you use DROP SYNONYM with a synonym, and multiple synonyms exist with the same name, the private synonym is dropped. If you issue the DROP SYNONYM statement again, the public synonym is dropped.

If you create a synonym for a table that is not in the current database, and this table is dropped, the synonym stays in place. You can create a new synonym for the dropped table, with the name of the dropped table as the synonym name, which points to another external or remote table. In this way, you can move a table to a new location and chain synonyms together so that the original synonyms remain valid. (You can chain as many as 16 synonyms in this manner.)

The following steps chain two synonyms together for the customer table, which will ultimately reside on the zoo database server (the CREATE TABLE statements are not complete):

1. In the stores_demo database on the database server that is called training, issue the following statement:
CREATE TABLE customer (lname CHAR(15)...)
2. On the database server called accntg, issue the following statement:
CREATE SYNONYM cust FOR stores_demo@training:customer
3. On the database server called zoo, issue the following statement:
CREATE TABLE customer (lname CHAR(15)...)
4. On the database server called training, issue the following statement:
DROP TABLE customer
CREATE SYNONYM customer FOR stores_demo@zoo:customer

The synonym cust on the accntg database server now points to the customer table on the zoo database server.

The following steps show an example of chaining two synonyms together and changing the table to which a synonym points:

1. On the database server called training, issue the following statement:
CREATE TABLE customer (lname CHAR(15)...)
2. On the database server called accntg, issue the following statement:
CREATE SYNONYM cust FOR stores_demo@training:customer
3. On the database server called training, issue the following statement:
DROP TABLE customer
CREATE TABLE customer (lastname CHAR(20)...)

The synonym cust on the accntg database server now points to a new version of the customer table on the training database server.

Related statement: DROP SYNONYM

For a discussion of concepts related to synonyms, see the Informix Guide to Database Design and Implementation.