1. Derby
  2. DERBY-335

Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or a table.


    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s:
    • Fix Version/s:
    • Component/s: SQL
    • Labels:
    • Environment:


      Synonym provides an alternate name for a table or a view that is present in the same schema or another schema. A synonym can also be created for another synonym, causing nesting of synonyms. A synonym can be used in SELECT, INSERT, UPDATE, DELETE or LOCK TABLE statements instead of the original qualified table or view name. Note that a synonym can be created for a table or a view that doesn't yet exists. But the target table/view must be present before the synonym can be used.

      Synonyms are supported by all major database vendors, including Oracle, DB2 and mySQL. DB2 also allows CREATE ALIAS statement, which does exactly same as CREATE SYNONYM. Creating aliases instead of synonyms is not supported by Oracle or mySQL, so I propose that Derby not support creating aliases. Synonyms are not part of SQL-2003 spec, but is a common-SQL statement among major database vendors. SQL standard doesn't pay attention to DDLs as much, so I suspect they skipped synonyms.

      I will be adding two new DDL statements to Derby:

      CREATE SYNONYM <SynonymSchema>.<SynonymName> FOR <TargetSchema>.<TargetName>
      DROP SYNONYM <SynonymSchema>.<SynonymName>

      Synonyms share the same namespace as tables or views. It is not possible to create a synonym with same name as a table that already exists in the same schema. Similarly, a table/view can't be created that matches a synonym already present.

      1. synonym.patch.jira
        59 kB
        Satheesh Bandaram

        Issue Links


          Satheesh Bandaram created issue -
          Satheesh Bandaram made changes -
          Field Original Value New Value
          Attachment synonym.patch.jira [ 12310549 ]
          Satheesh Bandaram made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Resolved [ 5 ]
          Satheesh Bandaram made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Issue Type New Feature [ 2 ] Improvement [ 4 ]
          Rick Hillegas made changes -
          Link This issue is related to DERBY-6109 [ DERBY-6109 ]
          Gavin made changes -
          Workflow jira [ 12321064 ] Default workflow, editable Closed status [ 12802435 ]


            • Assignee:
              Satheesh Bandaram
              Satheesh Bandaram
            • Votes:
              0 Vote for this issue
              0 Start watching this issue


              • Created: