Chapter 1: System Procedures

sp_commonkey

Defines a common key—columns that are frequently joined—between two tables or views.

sp_commonkey tabaname, tabbname, col1a, col1b 
	[, col2a, col2b, ..., col8a, col8b]

col1a: is the name of the first column in the table or view tabaname that makes up the common key. Specify at least one pair of columns (one column from the first table or view and one from the second table or view).

col1b: is the name of the partner column in the table or view tabbname that is joined with col1a in the table or view tabaname.

Defines a common key on titles.titleid and titleauthor.titleid:

sp_commonkey titles, titleauthor, title_id, title_id

Assumes two tables, projects and departments, each with a column named empid. This statement defines a frequently used join on the two columns:

sp_commonkey projects, departments, empid, empid

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. sp_commonkey does not enforce referential integrity constraints; use the primary key and foreign key clauses of the create table or alter table command to enforce key relationships.
Executing sp_commonkey adds the key to the syskeys system table. To display a report on the common keys that have been defined, use sp_helpkey.
You must be the owner of at least one of the two tables or views in order to define a common key between them.
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 common key. The datatypes of the common columns must also agree. For columns that take a length specification, the lengths can differ. The null types of the common columns need not agree.
The installation process runs sp_commonkey on appropriate columns of the system tables.
You cannot use a Java datatype with sp_commonnkey.

Only the owner of tabaname or tabbname can execute sp_commonkey.

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect