[DERBY-335] Enhance Derby by adding SYNONYM support. A synonym is an alternate name for a view or a table.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.1.0
• Fix Version/s: 10.1.1.0
• Component/s: SQL
• Environment: Generic
• Assignee: Satheesh E. Bandaram
• Reporter: Satheesh E. Bandaram
• Created: 05/Jun/05 08:52
• Updated: 13/Mar/13 17:58
• Resolved: 15/Jun/05 03:48

Description

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.

Issue Links

• is related to: DERBY-6109 Allow CREATE SYNONYM for table function invocations.

Sub-Tasks

• Document SYNONYM support: Closed