The CREATE SYNONYM statement in SQL is used to create a synonym for a database object, such as a table, view, sequence, or another synonym. A synonym provides an alias for the object and allows users to reference the object without needing to specify its full name, which can simplify SQL queries and improve readability.
The basic syntax for creating a synonym is as follows:
CREATE [OR REPLACE] SYNONYM synonym_name FOR object_name;
Parameters:
- synonym_name: The name you want to assign to the synonym. This is the name by which you will refer to the synonym in your SQL queries.
- object_name: The full name of the database object for which you are creating the synonym. This should include the schema name if the object is not in the default schema.
Example:
Let’s say you have a table called Employees in the schema HR, and you want to create a synonym for it called Emp.
CREATE SYNONYM Emp FOR HR.Employees;
After you execute this statement, you can refer to the Employees table simply as Emp in subsequent SQL statements:
SELECT * FROM Emp;
Additional Notes:
- If you use the OR REPLACE clause, it will create the synonym if it does not exist or replace it if it already exists.
- Synonyms are often used to simplify access to complex object names, facilitate code portability, or reduce the impact of object name changes.
- In some databases, such as Oracle, you can create public synonyms that are accessible to all users, or private synonyms that are accessible only by the user who created them.
Example of Creating a Public Synonym:
CREATE PUBLIC SYNONYM Emp FOR HR.Employees;
This command creates a synonym that can be used by any user in the database.
Dropping a Synonym:
If you no longer need a synonym, you can drop it using the following command:
DROP SYNONYM synonym_name;
For example:
DROP SYNONYM Emp;
With these commands, you can easily create and manage synonyms for database objects in your SQL environment.