Chapter 1: System Procedures

sp_dropkey

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

sp_dropkey keytype, tabname [, deptabname]

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.

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

Drops the common keys between the employees and projects tables:

sp_dropkey common, employees, projects

Drops the foreign key between the titleauthor and titles tables:

sp_dropkey foreign, titleauthor, titles

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.

Only the owner of tabname can execute sp_dropkey.

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