In SQL, the CREATE SYNONYM statement is used to create a synonym for a database object, such as a table, view, sequence, or stored procedure. A synonym acts as an alias or alternative name for the underlying object, which can simplify access to that object, especially if it's in a different schema or database.
Here’s the general syntax for creating a synonym:
CREATE [OR REPLACE] SYNONYM synonym_name FOR object_name;
- synonym_name: The name you want to give to the synonym.
- object_name: The name of the object you are creating a synonym for, which may include the schema name (e.g., schema_name.object_name).
Example Usage
1. Creating a Synonym for a Table:
Suppose there is a table named employees in the schema hr. You can create a synonym called emp for easier access:
CREATE SYNONYM emp FOR hr.employees;
Now, you can use emp instead of hr.employees in your SQL queries:
SELECT * FROM emp;
2. Creating a Synonym for a View:
If you have a view named sales_summary in the schema sales, you could create a synonym like this:
CREATE SYNONYM summary FOR sales.sales_summary;
Then refer to the view using the synonym:
SELECT * FROM summary;
3. Using OR REPLACE:
If you want to replace an existing synonym, you can use the OR REPLACE option:
CREATE OR REPLACE SYNONYM emp FOR hr.all_employees; -- Replaces existing synonym if it exists
Additional Notes
- Privileges: Make sure you have the necessary privileges to create synonyms, particularly if you are creating a synonym for an object owned by another user.
- Schema: If you do not specify a schema when creating a synonym, the synonym is created in your own schema.
- Synonyms and Dependencies: When you use a synonym, the underlying object can change (for example, the object can be dropped or recreated) without affecting the synonym itself directly. However, if the object is dropped, the synonym will become invalid.
Using synonyms can help simplify access to complex object names in your SQL queries and make your code cleaner and easier to read.