Drop and recreate constraint

Question

How to drop and recreate IX_DimCountry CONSTRAINT

CREATE TABLE [WarehouseMgmt].[DimCountry] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [IX_DimCountry] UNIQUE NONCLUSTERED ([Id] ASC) ON [PRIMARY]

and to have this

CREATE TABLE [WarehouseMgmt].[DimCountry] (
    [Id]           INT            NOT NULL,
    CONSTRAINT [IX_DimCountry] UNIQUE NONCLUSTERED ([Id] ASC) ON [WH_IX]

And also is there a way just to alter Id to not be IDENTITY anymore?

Possible duplicate of [How to Alter Constraint](http://stackoverflow.com/questions/13244889/how-to-alter-constraint) — CiucaS, Apr 20 '16 at 09:48
Please refer this post: [How to remove auto increment from table in sql server 2012](http://stackoverflow.com/questions/23511309/how-to-remove-auto-increment-from-table-in-sql-server-2012) — irakliG., Apr 20 '16 at 09:51

score 1 · Answer 1 · answered Apr 20 '16 at 09:54

If you need to keep the data, but remove the IDENTITY column, you will need to:

Create a new column Transfer the data from the existing IDENTITY column to the new column Drop the existing IDENTITY column. Rename the new column to the original column name

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

ALTER TABLE [WarehouseMgmt].[DimCountry] DROP CONSTRAINT IX_DimCountry;


ALTER TABLE [WarehouseMgmt].[DimCountry]
ADD CONSTRAINT IX_DimCountry UNIQUE(id)

You cannot Remove IDENTITY.

Remove Identity from a column in a table

edited May 23 '17 at 11:52

Community

1
1

answered Apr 20 '16 at 09:56

Ponmani Chinnaswamy

705
7
21

Drop and recreate constraint

2 Answers2