1

I would like to drop the primary key for a table, but keep the column (I know the name of the column, if it helps).

I use this script to get the name of the primary key:

-- Return the name of primary key.  
SELECT key_name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = 'my_table';

Then I remove the key like this:

ALTER TABLE Production.TransactionHistoryArchive  
DROP CONSTRAINT key_name ;

It works, but I have to run this script in one go. How it is possible to combine drop constraint with a select query?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Iter Ator
  • 8,226
  • 20
  • 73
  • 164
  • 1
    *"How it is possible to combine drop constraint with a select query?"* those are 2 completely different types of statement; you can't. – Thom A Apr 09 '21 at 10:02
  • 4
    And this is why you should choose to supply names for constraints when you create them - if you'd done so, you wouldn't need the first query at all. – Damien_The_Unbeliever Apr 09 '21 at 10:02
  • @Damien_The_Unbeliever I am not the one, who created the tables and keys. I just have to deal with it. – Iter Ator Apr 09 '21 at 10:03
  • You will need to use Dynamic SQL to be able to do that in a single query. Of course, specifying the name for your constraint would make your life easier. – Stu Apr 09 '21 at 10:04
  • I thought this felt familiar. Basically answered a very similar question a couple of days ago: [Removing a constraint in T-SQL by alter table command - problem](https://stackoverflow.com/a/66968014/2029983) – Thom A Apr 09 '21 at 10:04
  • Even with dynamic SQL though, @Stu , the OP would have a `SELECT`/`SET` and `EXEC` statement. They seem to want some automagic single statement process that does the job when they don't know the name of the object; which simply doesn't exist. – Thom A Apr 09 '21 at 10:06
  • That's true and unavoidably the case, but possibly they could create a procedure to take the name of a table for dropping its PK – Stu Apr 09 '21 at 10:13

1 Answers1

1

Will throw this out there in case it's useful to you. Using Dynamic SQL you can build something that you could easily create a procedure for.

declare @Schema nvarchar(20)=N'Production',
  @Table nvarchar(50)=N'TransactionHistoryArchive',
  @sql nvarchar(100)=''

select @sql='ALTER TABLE ' 
  + QuoteName(@Schema) + '.'
  + QuoteName(@Table) 
  + ' DROP CONSTRAINT ' 
  + QuoteName(name)
from sys.key_constraints  
where type = 'PK' and Object_Name(parent_object_id) = @Table and [schema_id]=Schema_Id(@Schema)

print @sql
exec sp_executesql @sql
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Note that if the OP has a table with the same name on different schemas this *could* result in an error. I also suggest using `sys.sp_executesql @SQL` over `EXEC(@SQL)` as the former is able to be parametrised, and so promotes the use of it for when you do need a parametrised dynamic statement. – Thom A Apr 09 '21 at 10:22
  • @Larnu Yes that's true I have added the schema in to above for clarity. – Stu Apr 09 '21 at 10:26
  • Probably better to do it the other way round `parent_object_id = OBJECT_ID(@TableWithSchemaName)` – Charlieface Apr 12 '21 at 09:29