SQL Bible [Electronic resources] نسخه متنی

Aliases and Synonyms

Different databases are organized in quite
different ways. Even the word database itself has
completely different meanings in different RDBMS implementations. For example,
an Oracle database is a totally self-contained and independent entity with its
own set of users, tables, indexes, and other objects invisible to other
databases. Each Oracle database user can have his/her own tables, views,
indexes, and so on. (In Oracle terms
USER and
SCHEMA are often used as synonyms which
adds confusion.) To access objects that belong to another user (or are within
another schema), you have to be granted appropriate permissions (see
Chapter
12) and you also have to use fully qualified names
(schema_name.object_name). For example, if
USER1 wants to select records from
SHIPMENT table that belongs to
USER3, the query would look like this:

SELECT * FROM
USER3.SHIPMENT;

Assuming
SHIPMENT always means
USER3.SHIPMENT for
USER1, typing fully qualified name makes
queries longer and less readable.

Figure 4-6 illustrates Oracle's database
organization.

Figure 4-6: Database organization
in Oracle

The Oracle RDBMS lets you to create
synonyms to deal with the problem. A synonym is a name
that translates into another name whenever it is referenced. In other words, we
can say that a synonym is an alternative name for a database object. You can
create the synonym
SHIPMENT for
USER3.SHIPMENT and use it any time you need
to access the
USER3.SHIPMENT table.

DB2 UDB organization is quite similar; a
database object that is simply a different name for another database object is
called
ALIAS.

The structure of MS SQL Server is
different. There can be many databases within a single SQL Server. Users (or
logins) are created on the server level and can have
access to many databases while the database objects belong to a single owner
(usually called dbo). See
Figure
4-7.

Figure 4-7: Database organization
in MS SQL Server

SQL99

Synonyms and aliases are not a part of
SQL99 standards.

Oracle 9i
CREATE SYNONYM statement

The syntax for the
CREATE
SYNONYM statement is

CREATE [PUBLIC] SYNONYM
[<schema>.]<synonym_name> FOR
[<schema>.]<object_name>[@<dblink>];

Public versus private
synonyms

In Oracle, you can create public
synonyms accessible to all database users or private ones visible only to their
owners. Use keyword
PUBLIC if you want to create a public
synonym or skip it otherwise. (Keyword
PRIVATE is invalid.)

Types of objects you can create
synonyms for

You can create synonyms for the
following Oracle objects: table, view, sequence, stored procedure, function,
package, materialized view, and Java class schema object. You can also create a
synonym for another synonym.

Creating synonyms for remote
database objects

You can create synonyms for objects
located in remote databases assuming a database link
exists for those databases. More about database links later in this
chapter.

CREATE SYNONYM examples

The following example creates the
public synonym
SHIPMENT for a hypothetical table
USER3.SHIPMENT:

CREATE PUBLIC SYNONYM
shipment FOR user3.shipment;

The next statement illustrates the
creation of private synonym
EMP for a
USERn.EMPLOYEE table in
USER2 schema:

CREATE SYNONYM user2.emp FOR
usern.employee;

Note that you could skip
USER2 if the above statement was issued
by
USER2 him/herself; it is mandatory
though if the synonym
EMP is being created by, say, the
database administrator for
USER2.

DB2 UDB 8.1 CREATE ALIAS/SYNONYM
statement

You can use either the
CREATE
SYNONYM or the
CREATE
ALIAS statement. Aliases can be created
for tables, views, or other aliases. The syntax is

CREATE {ALIAS | SYNONYM}
<alias_name> FOR <object_name>

In DB2, you cannot create an alias with
a name identical to one of a table, view, or another alias that already exists
in the current database. That resolves the problem described in the
previous
section about Oracle's synonyms, but makes using aliases in DB2 less
flexible.

Here are examples that create aliases in
DB2:

CREATE ALIAS shipmt FOR
user3.shipment 

CREATE ALIAS emp FOR
usern.employee

Note that the following statement
returns an error (assuming you are using objects shown in
Figure
4-6) because the table named
SHIPMENT exists in schema
USER3:

CREATE PUBLIC SYNONYM SHIPMENT
FOR USER3.SHIPMENT;

Like Oracle, DB2 allows you to create
synonyms for objects that do not yet exist, though a warning will be
issued.

MS SQL Server 2000

MS SQL Server does not let you create
aliases or synonyms. This limitation is justified by its database structure
(Figure
4-7).