none
How to disable and enable Unique Constraint of a table RRS feed

  • Question

  • Hi Guys,

    Is there a way to disable and enable the Unique Constraint of a table ?

    I have you the followings commands but it only effective for foreign key constraint and not Unique or PK.

    ALTER TABLE [dbo].[CIS_AUD_DTL_H] NOCHECK CONSTRAINT ALL

    ALTER TABLE [dbo].[CIS_AUD_DTL_H] CHECK CONSTRAINT ALL

    Please help.


    Thank You

    Tuesday, June 7, 2016 9:21 AM

Answers

  • Hi,

    We do not have any way to disable/enable unique constraints. We can only do drop and recreate unique constraints.

    We can only do  constraint disable for FK, CHECK constraints

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao G, MCSE(Business Intelligence) Blog:http://sqlcart.blogspot.com

    • Marked as answer by KRGuy Tuesday, June 7, 2016 10:18 AM
    Tuesday, June 7, 2016 10:00 AM
  • Yes, it can. Exactly in the same way. However, beware that if you disable the clustered index on the table, you cannot write to it.

    • Marked as answer by KRGuy Monday, June 13, 2016 7:40 AM
    Wednesday, June 8, 2016 4:13 PM
  • This action applies only to foreign key and check constraints

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by KRGuy Tuesday, June 7, 2016 10:18 AM
    Tuesday, June 7, 2016 9:31 AM
    Answerer
  • I would like to temporary disable the unique constraint before executing a bulk insert into some tables

    and then enable back the unique constraint.

    But it seems that MSSQL 2012 does not have that feature available.

    No, you can do it:

    CREATE TABLE bludder(pk int NOT NULL PRIMARY KEY,
                         a int NOT NULL CONSTRAINT u_bludder UNIQUE NONCLUSTERED(a))
    go
    INSERT bludder(pk, a) VALUES(1, 1), (2, 2), (3, 4)
    go
    ALTER INDEX u_bludder ON bludder DISABLE
    go
    INSERT bludder(pk, a) VALUES(11, 1), (12, 2), (13, 4)
    go
    ALTER INDEX u_bludder ON bludder REBUILD
    go
    DELETE bludder WHERE pk < 10
    go
    ALTER INDEX u_bludder ON bludder REBUILD
    go
    DROP TABLE bludder

    But you need to det rid of the duplicates before you re-enable the constraint.

    • Marked as answer by KRGuy Wednesday, June 8, 2016 9:28 AM
    Tuesday, June 7, 2016 12:34 PM
  • You can disable a non-clustered index, including constraints, as demonstrated below.

    CREATE TABLE tempdisable (pk int NOT NULL PRIMARY KEY,
                              u  int NOT NULL CONSTRAINT uu UNIQUE NONCLUSTERED(u))
    go
    INSERT tempdisable(pk, u) VALUES(1, 1), (2, 2), (3, 3)
    go
    ALTER INDEX uu ON tempdisable DISABLE
    go
    INSERT tempdisable(pk, u) VALUES(11, 1), (12, 2), (13, 3)
    go
    SELECT * FROM tempdisable WITH (INDEX = uu) WHERE u = 3
    go
    --ALTER INDEX uu ON tempdsiable REBUILD
    go
    DELETE tempdisable WHERE pk < 10
    go
    ALTER INDEX uu ON tempdisable REBUILD
    go
    SELECT * FROM tempdisable WITH (INDEX = uu) WHERE u = 3
    go
    DROP TABLE tempdisable


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by KRGuy Wednesday, June 8, 2016 9:29 AM
    Tuesday, June 7, 2016 9:48 PM

All replies

  • This action applies only to foreign key and check constraints

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by KRGuy Tuesday, June 7, 2016 10:18 AM
    Tuesday, June 7, 2016 9:31 AM
    Answerer
  • Hi,

    We do not have any way to disable/enable unique constraints. We can only do drop and recreate unique constraints.

    We can only do  constraint disable for FK, CHECK constraints

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao G, MCSE(Business Intelligence) Blog:http://sqlcart.blogspot.com

    • Marked as answer by KRGuy Tuesday, June 7, 2016 10:18 AM
    Tuesday, June 7, 2016 10:00 AM
  • You can use ALTER INDEX DISABLE, but what more exactly do you want to achieve?

    Tuesday, June 7, 2016 10:19 AM
  • Hi Erland,

    I would like to temporary disable the unique constraint before executing a bulk insert into some tables

    and then enable back the unique constraint.

    But it seems that MSSQL 2012 does not have that feature available.

    Thanks.


    Thank You

    Tuesday, June 7, 2016 11:14 AM
  • I would like to temporary disable the unique constraint before executing a bulk insert into some tables

    and then enable back the unique constraint.

    But it seems that MSSQL 2012 does not have that feature available.

    No, you can do it:

    CREATE TABLE bludder(pk int NOT NULL PRIMARY KEY,
                         a int NOT NULL CONSTRAINT u_bludder UNIQUE NONCLUSTERED(a))
    go
    INSERT bludder(pk, a) VALUES(1, 1), (2, 2), (3, 4)
    go
    ALTER INDEX u_bludder ON bludder DISABLE
    go
    INSERT bludder(pk, a) VALUES(11, 1), (12, 2), (13, 4)
    go
    ALTER INDEX u_bludder ON bludder REBUILD
    go
    DELETE bludder WHERE pk < 10
    go
    ALTER INDEX u_bludder ON bludder REBUILD
    go
    DROP TABLE bludder

    But you need to det rid of the duplicates before you re-enable the constraint.

    • Marked as answer by KRGuy Wednesday, June 8, 2016 9:28 AM
    Tuesday, June 7, 2016 12:34 PM
  • You can disable a non-clustered index, including constraints, as demonstrated below.

    CREATE TABLE tempdisable (pk int NOT NULL PRIMARY KEY,
                              u  int NOT NULL CONSTRAINT uu UNIQUE NONCLUSTERED(u))
    go
    INSERT tempdisable(pk, u) VALUES(1, 1), (2, 2), (3, 3)
    go
    ALTER INDEX uu ON tempdisable DISABLE
    go
    INSERT tempdisable(pk, u) VALUES(11, 1), (12, 2), (13, 3)
    go
    SELECT * FROM tempdisable WITH (INDEX = uu) WHERE u = 3
    go
    --ALTER INDEX uu ON tempdsiable REBUILD
    go
    DELETE tempdisable WHERE pk < 10
    go
    ALTER INDEX uu ON tempdisable REBUILD
    go
    SELECT * FROM tempdisable WITH (INDEX = uu) WHERE u = 3
    go
    DROP TABLE tempdisable


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by KRGuy Wednesday, June 8, 2016 9:29 AM
    Tuesday, June 7, 2016 9:48 PM
  • Hi Erland,

    Can the same be used to disable the Primary Key contraint too?

    If yes how?

    Thanks


    Thank You

    Wednesday, June 8, 2016 9:30 AM
  • Yes, it can. Exactly in the same way. However, beware that if you disable the clustered index on the table, you cannot write to it.

    • Marked as answer by KRGuy Monday, June 13, 2016 7:40 AM
    Wednesday, June 8, 2016 4:13 PM