Use synonyms for table names

A synonym is a name that you can use in place of another SQL identifier. You use the CREATE SYNONYM statement to declare an alternative name for a table, a view, or (for ) a sequence object.

Typically, you use a synonym to refer to tables that are not in the current database. For example, you might execute the following statements to create synonyms for the customer and orders table names:

CREATE SYNONYM mcust FOR masterdb@central:customer;
CREATE SYNONYM bords FOR sales@boston:orders;

After you create the synonym, you can use it in many contexts where the original table name is valid, as the following example shows:

SELECT bords.order_num, mcust.fname, mcust.lname
   FROM mcust, bords
   WHERE mcust.customer_num = bords.Customer_num
   INTO TEMP mycopy;

The CREATE SYNONYM statement stores the synonym name in the system catalog table syssyntable in the current database. The synonym is available to any query made in that database. (If the USETABLENAME environment variable is set, however, some DDL statements of SQL do not support synonyms in place of table names.)

A short synonym makes it easier to write queries, but synonyms can play another role. They allow you to move a table to a different database, or even to a different computer, and keep your queries the same.

Suppose you have several queries that refer to the tables customer and orders. The queries are embedded in programs, forms, and reports. The tables are part of the demonstration database, which is kept on database server avignon.

Now you decide to make the same programs, forms, and reports available to users of a different computer on the network (database server nantes). Those users have a database that contains a table named orders that contains the orders at their location, but they must have access to the table customer at avignon.

To those users, the customer table is external. Does this mean you must prepare special versions of the programs and reports, versions in which the customer table is qualified with a database server name? A better solution is to create a synonym in the users' database, as the following example shows:

DATABASE stores_demo@nantes;
CREATE SYNONYM customer FOR stores_demo@avignon:customer;

When the stored queries are executed in your database, the name customer refers to the actual table. When they are executed in the other database, the name is resolved through the synonym into a reference to the table that exists on the database server avignon. (In a database that is not ANSI-compliant, a synonym must be unique among the names of synonyms, tables, views, and sequence objects in the database. In an ANSI-compliant database, the owner.synonym combination must be unique within the namespace of objects that have been registered in the database with a tabid value.)