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]

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.

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
```

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.

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

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
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