sp_commonkey

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]

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 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.

Example 1 – Defines a common key on titles.titleid and titleauthor.titleid:
```
sp_commonkey titles, titleauthor, title_id, title_id
```
Example 2 – 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
```

There are additional considerations when using sp_commonkey:

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.

See also alter table, create table, create trigger in Reference Manual: Commands.

You must be the table owner to execute sp_commonkey. Permission checks do not differ based on the granular permissions settings

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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