You can delete (drop) a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. When the primary key is deleted, the corresponding index is deleted. This may be the clustered index of the table, causing the table to become a heap.
If a primary key exists, it is dropped. The user need not drop the primary key before dropping the table. If the primary key of the table is referenced by a constraint belonging to another table, then the table is not dropped and an error is signaled.
You'll have to use dynamic SQL for that, since ALTER TABLE does not accept variables or subqueries.
CREATE TABLE PKTest ( ID INT PRIMARY KEY ) ;
DECLARE @SQL VARCHAR(4000)
SET @SQL = 'ALTER TABLE PKTEST DROP CONSTRAINT |ConstraintName| '
SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT name
FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = OBJECT_ID('PKTest')
))
EXEC (@SQL)
DROP TABLE PKTest
Adding to Stuart Ainsworth answer, I do not know if PK name has to be unique across different schemas (if so, that answer is ok). Anyway I would choose different sub query for PK name, allowing explicitly to define schema:
declare @PrimaryKeyName sysname =
(select CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'PRIMARY KEY' and TABLE_SCHEMA='dbo' and TABLE_NAME = 'PKTest'
)
IF @PrimaryKeyName is not null
begin
declare @SQL_PK NVARCHAR(MAX) = 'alter table dbo.PKTest drop constraint ' + @PrimaryKeyName
print (@SQL_PK)
EXEC sp_executesql @SQL_PK;
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With