CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008-r2sql-server-2012
abc
abc

Reputation: 31

Can we drop and recreate primary key in SQL Server table @ Production environment? Do we have to down the server for it or we can do it live?

Can we drop and recreate a composite primary key in a SQL Server table in production environment? Do we have take down the server for it, or can we do it live?

Because we have to add more columns to the primary key. If we done it in live what are the problems we have to face?

Upvotes: 1

Views: 13717

Answers (1)

Mahajan344
Mahajan344

Reputation: 2550

You can remove primary key from table. This will also remove clustered index of that table if you haven't mentioned explicitly on other column.

To remove primary key run below query

-- Drop CHECK CONSTRAINT from the table
     ALTER TABLE /*schema*/./*table*/
        DROP CONSTRAINT /*constraint_name*/
     GO

and to add primary key run below code

-- Add a new CHECK CONSTRAINT to the table
ALTER TABLE /*schema*/./*table*/
    ADD CONSTRAINT /*contraint_name*/ /*constraint_type*/ (/*constraint_column_name*/ /*logical_expression*/)
GO

Upvotes: 3

Related Questions