The names of database objects need not be unique in a database. However, column names and index names must be unique within a table, and other object names must be unique for each owner within a database. Database names must be unique in Adaptive Server.
If you try to create a column using a name that is not unique in the table, or to create another database object, such as a table, a view, or a stored procedure, with a name that you have already used in the same database, Adaptive Server responds with an error message.
You can uniquely identify a table or column by adding other names that qualify it. The database name, the owner’s name, and, for a column, the table name or view name may be used to create a unique ID. Each of these qualifiers is separated from the next by a period.
For example, if the user “sharon” owns the authors table in the pubs2 database, the unique identifier of the city column in that table is:
pubs2.sharon.authors.city
The same naming syntax applies to other database objects. You can refer to any object in a similar fashion:
pubs2.dbo.titleview
dbo.postalcoderule
If the quoted_identifier option of the set command is on, you can use double quotes around individual parts of a qualified object name. Use a separate pair of quotes for each qualifier that requires quotes. For example, use:
database.owner."table_name"."column_name"
rather than:
database.owner."table_name.column_name"
The full naming syntax is not always allowed in create statements because you cannot create a view, procedure, rule, default, or trigger in a database other than the one you are currently in. The naming conventions are indicated in the syntax as:
[[database.]owner.]object_name
or:
[owner.]object_name
The default value for owner is the current user, and the default value for database is the current database. When you reference an object in any SQL statement, other than a create statement, without qualifying it with the database name and owner name, Adaptive Server first looks at all the objects you own, and then at the objects owned by the database owner. As long as Adaptive Server has enough information to identify an object, you need not type every element of its name. You can omit intermediate elements and indicate their positions with periods:
database..table_name
In the example above, you must include the starting element if you are using this syntax to create tables. If you omit the starting element, a table named ..mytable is created. The naming convention prevents you from performing certain actions on such a table, such as cursor updates.
When qualifying a column name and a table name in the same statement, use the same naming abbreviations for each; they are evaluated as strings and must match, or an error is returned. Here are two examples with different entries for the column name. The second example is incorrect, and cannot execute, because the syntax for the column name does not match the syntax for the table name.
select pubs2.dbo.publishers.city from pubs2.dbo.publishers
city ----------------------- Boston Washington Berkeley
select pubs2.sa.publishers.city from pubs2..publishers
The column prefix "pubs2.sa.publishers" does not match a table name or alias name used in the query.
