sp_dropkey

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

Examples

Usage

There are additional considerations when using sp_dropkey:
  • Executing sp_dropkey deletes the specified key from syskeys. Only the owner of a table can drop a key from that table.

  • Keys are created to make explicit a logical relationship that is implicit in your database design. This information can be used by an application.

  • Dropping a primary key automatically drops any foreign keys associated with it. Dropping a foreign key has no effect on a primary key specified on that table.

  • Executing sp_commonkey, sp_primarykey, or sp_foreignkey adds the key to the syskeys system table. To display a report on the keys that have been defined, execute sp_helpkey.

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:

InformationValues
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

Related reference
sp_commonkey
sp_foreignkey
sp_helpkey
sp_primarykey