Transfer primary key using script to SQL Server database

Question

I need to delete the primary key of the Student table in the SQL Server database.

I edited in the table and the script got

 ALTER TABLE dbo.Student DROP CONSTRAINT PK__Student__9CC368536561EF8B 

But when I ran this script in SQL Server Query Browser to remove the primary key

Message is displayed

Msg 3728, Level 16, State 1, Line 1
"PK__Student__9CC368536561EF8B" is not a limitation.
Msg 3727, Level 16, State 0, Line 1

To my concern, I think that PK__Student__9CC368536561EF8B will be randomly generated, please help me reset the primary key constraint using a script .

Thank you in advance

Answer 1

You can find the name of the constraint in the sys.key_constraints table:

 SELECT name FROM sys.key_constraints WHERE [type] = 'PK' AND [parent_object_id] = Object_id('dbo.Student'); 

If you are not interested in the name, but just want to remove it, you can use a combination of this and dynamic sql:

 DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX); SELECT @table = N'dbo.Student'; SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + name + ';' FROM sys.key_constraints WHERE [type] = 'PK' AND [parent_object_id] = OBJECT_ID(@table); EXEC sp_executeSQL @sql; 

This code belongs to Aaron Bertrand ( source ).

Answer 2

just click

Database> tables> your table name> keys> copy restrictions like "PK__TableName__30242045"

and execute the following query:

 Query:alter Table 'TableName' drop constraint PK__TableName__30242045 

Answer 3

The answer I received is that the variables and subqueries will not work, and we have a custom dynamic SQL script. The following works:

 DECLARE @SQL VARCHAR(4000) SET @SQL = 'ALTER TABLE dbo.Student DROP CONSTRAINT |ConstraintName| ' SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID('Student'))) EXEC (@SQL)