Chaining Synonyms

About this task

If you create a synonym for a table or view that is not in the current database, and this table or view is dropped, the synonym remains registered in the system catalog. You can create a new synonym whose identifier is the name of the dropped table or view, but that points to a table or view in the current database (or in another database).

In this way, after you rename a table, or after you move a table or view to another database location, you can chain synonyms together so that the original synonym remains valid in existing applications. You can chain up to 16 synonyms in this manner.

Chaining synonyms to reference a relocated table object is possible for tables or views, but this is not valid for synonyms that point to a sequence object, because CREATE SYNONYM can define synonyms only for sequences that are registered in the current database.

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

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

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:

On the database server called training, issue the following statement:
```
CREATE TABLE customer (lname CHAR(15)...);
```
On the database server called accntg, issue the following statement:
```
CREATE SYNONYM cust FOR stores_demo@training:customer;
```

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.