Oninit
Talk to an expert →

Informix Error -559: Cannot create a synonym on top of another synonym.

Cause and resolution

Cannot create a synonym on top of another synonym.

This CREATE SYNONYM statement names another synonym. Making a chain of synonyms is not supported. See the discussion of error -218 for a way to list the names of all synonyms. To have a second synonym with the same meaning as the first, find out the meaning of the other synonym. Then make a new synonym to refer to the same base table. To see the base table for a given synonym, query systables and syssyntable as follows, substituting the name of the synonym:

SELECT T.tabname synonym, S.* FROM systables T, syssyntable S WHERE T.tabname = name AND T.tabid = S.tabid

If btabid is defined in the resulting display, the synonym refers to a table in that current database with that tabid; otherwise, it refers to an external database.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-559 fires when CREATE SYNONYM names another synonym as its target rather than a base table — Informix doesn't support chaining synonyms; a synonym must point directly at a real table.

  1. CREATE SYNONYM given the name of an existing synonym instead of a base table — the direct, only cause.
  2. Confusion between a synonym and the table it represents, especially when the synonym's name is similar to or reused across environments where the underlying table name differs.
  3. A migration script that creates a new synonym pointing at an old synonym left over from a previous naming convention, instead of pointing both at the real table.
Solutions / Resolution
  1. Identify the base table the existing synonym actually refers to, per the official guidance:
    SELECT T.tabname synonym, S.* FROM systables T, syssyntable S
WHERE T.tabname = 'existing_synonym_name' AND T.tabid = S.tabid;
    per the official guidance, if btabid is defined in the result, the synonym refers to a table in the current database with that tabid; otherwise it refers to an external database.
  2. Create the new synonym pointing directly at that base table, per the official guidance, rather than at the intermediate synonym.
Examples
The disallowed chain
CREATE SYNONYM orders_syn FOR orders;
CREATE SYNONYM orders_syn2 FOR orders_syn;
-- -559: orders_syn is itself a synonym, not a base table
Resolving to the base table first
SELECT T.tabname synonym, S.* FROM systables T, syssyntable S
WHERE T.tabname = 'orders_syn' AND T.tabid = S.tabid;
-- confirms orders_syn -> orders (a base table, btabid defined)

CREATE SYNONYM orders_syn2 FOR orders;
Diagnostic Checks
  1. Run the official systables/syssyntable query against the target name before creating a synonym, to confirm it's a base table rather than another synonym.
Related Errors / Related Topics
  • -557 — "Cannot locate table that is external to the current database after level-count levels of synonym mapping." A related synonym-resolution error, about a chain that runs too deep across databases rather than a chain disallowed outright at creation time.

Synonyms can't chain — resolve to the real base table with the official systables/ syssyntable query, then point the new synonym there directly.