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


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




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:



Audit option


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

Related reference