sp_dropkey

Description

Removes from the syskeys table a key that had been defined using sp_primarykey, sp_foreignkey, or sp_commonkey.

Syntax

sp_dropkey keytype, tabname [, deptabname]

Parameters

keytype

is the type of key to be dropped. The keytype must be primary, foreign, or common.

tabname

is the name of the key table or view that contains the key to be dropped.

deptabname

specifies the name of the second table in the relationship, if the keytype is foreign or common. If the keytype is primary, this parameter is not needed, since primary keys have no dependent tables. If the keytype is foreign, this is the name of the primary key table. If the keytype is common, give the two table names in the order in which they appear with sp_helpkey.

Examples

Example 1

Drops the primary key for the employees table. Any foreign keys that were dependent on the primary key for employees are also dropped:

sp_dropkey primary, employees 

Example 2

Drops the common keys between the employees and projects tables:

sp_dropkey common, employees, projects

Example 3

Drops the foreign key between the titleauthor and titles tables:

sp_dropkey foreign, titleauthor, titles

Usage

Permissions

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

Auditing

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

See also

System procedures sp_commonkey, sp_foreignkey, sp_helpkey, sp_primarykey