1 Version 4.0 -- 1/15/89 sp_commonkey
______________________________________________________________________
NAME: sp_commonkey
FUNCTION:
Defines a common key-columns that are frequently joined-between
two tables or views.
SYNTAX:
sp_commonkey tabaname, tabbname, col1a, col1b
[, col2a, col2b, ..., col8a, col8b]
EXAMPLES:
sp_commonkey projects, departments, empid, empid
sp_commonkey Version 4.0 -- 1/15/89 2
______________________________________________________________________
Assume two tables, projects and departments, each with a column
named empid. This statement defines a frequently used join on
the two columns.
PARAMETERS:
tabaname - is the name of the first table or view to be joined.
tabbname - is the name of the second table or view to be joined.
col1a - is the name of the first column in table or view
tabaname that makes up the common key. At least one pair of
columns (one column from the first table or view, and one
from the second each table or view) must be specified.
The number of columns in each table or view must be the same,
and their datatypes must be the same. Their lengths and
nulltypes need not be the same. Up to eight columns from
each table or view can participate in the common key.
3 Version 4.0 -- 1/15/89 sp_commonkey
______________________________________________________________________
col1b - is the name of the partner column in table or view
tabbname that is joined with col1a in table or view tabaname.
COMMENTS:
o Common keys are created in order to make explicit a logical
relationship that is implicit in your database design. The
information can be used by an application.
o Executing sp_commonkey adds the key to the syskeys system
table. To display a report on the common keys that have been
defined, execute sp_helpkey.
o You must be the owner of at least one of the two tables or
views in order to define a common key between them.
o The number of columns from the first table or view must be the
same as the number of columns from the second table or view. Up
to eight columns from each table or view can participate in the
sp_commonkey Version 4.0 -- 1/15/89 4
______________________________________________________________________
common key.
o The datatypes of the common columns must agree. For columns
that take a length specification, the lengths can differ. The
nulltypes of the common columns need not agree.
o SQL Server installation scripts installmaster and installmodel
execute the sp_commonkey procedure on all system tables.
MESSAGES:
The common key between the specified tables or views has been
added to syskeys.
The table or view you gave as tabaname doesn't exist in the
current database.
The table or view you gave as tabbname doesn't exist in the
current database.
You aren't the owner of table or view tabaname.
Two cases generate this message: either the column pair that you
5 Version 4.0 -- 1/15/89 sp_commonkey
______________________________________________________________________
specified doesn't exist, or the columns in the pair are dif-
ferent types.
PERMISSIONS:
Execute permission to owner of tabaname or tabbname.
TABLES USED:
syscolumns, syskeys, sysobjects
SEE ALSO:
sp_dropkey, sp_foreignkey, sp_helpkey, sp_helpjoins,
sp_primarykey, Joins