Defines a common key—columns that are frequently joined—between two tables or views.
sp_commonkey tabaname, tabbname, col1a, col1b [, col2a, col2b, ..., col8a, col8b]
is the name of the first table or view to be joined.
is the name of the second table or view to be joined.
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).
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.
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:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Commands alter table, create table, create trigger
System procedures sp_dropkey, sp_foreignkey, sp_helpjoins, sp_helpkey, sp_primarykey
