Removes from the syskeys table a key that had been defined using sp_primarykey, sp_foreignkey, or sp_commonkey.
sp_dropkey keytype, tabname [, deptabname]
is the type of key to be dropped. The keytype must be primary, foreign, or common.
is the name of the key table or view that contains the key to be dropped.
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.
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:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_commonkey, sp_foreignkey, sp_helpkey, sp_primarykey
