Using Synonyms with Table Names

A synonym is a name that you can use in place of another name. You use the CREATE SYNONYM statement to provide an alternative name for a table or view.

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 anywhere in the current database that you might use the original table name, 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.

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 need 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 translated through the synonym into a reference to the table that exists on the database server avignon.