disable primary key constraints in table

• e1785

  Right there with Babe

  Points: 758

  More actions

  December 13, 2010 at 3:28 pm

  #244319

  I have a staging database that I use to load up all of my data and see where constraints fail. So I disable constraints using commands like:

  ALTER TABLE database.schema.TABLENAME NOCHECK CONSTRAINT MY_CONSTRAINT_NAME;

  And then load up my data and run:

  DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;

  I would like to be able to do the same thing with primary key constraints but it looks like there isn't an option. Am I missing something?

• Meet George Jetson

  SSChampion

  Points: 11710

  More actions

  December 13, 2010 at 3:40 pm

  #1261967

  ALTER TABLE InsertTableName DROP CONSTRAINT PK_Name

  To reenable it:

  ALTER TABLE InsertTableName ADD CONSTRAINT PK_Name PRIMARY KEY /* CLUSTERED */ (pk_column)

  Chris Powell

  George: You're kidding.
  Elroy: Nope.
  George: Then lie to me and say you're kidding.

• e1785

  Right there with Babe

  Points: 758

  More actions

  December 13, 2010 at 3:48 pm

  #1261973

  OK. But I take it there's no way to keep it as a "disabled" constraint, as you can with FK checks? The goal is to have the same sort of handy output that I can get with DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS, so I can see the bad data.

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  December 13, 2010 at 10:50 pm

  #1262036

  e1785 (12/13/2010)

  ---

  OK. But I take it there's no way to keep it as a "disabled" constraint, as you can with FK checks? The goal is to have the same sort of handy output that I can get with DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS, so I can see the bad data.

  see the results

  create table textpk

  ( id int primary key

  )

  create table textfk

  ( id1 int )

  ALTER TABLE textfk

  ADD CONSTRAINT FK FOREIGN KEY (id1)

  REFERENCES textpk (ID) ;

  ALTER TABLE textpk

  drop CONSTRAINT PK__textpk__27E3AA06

  Msg 3725, Level 16, State 0, Line 2

  The constraint 'PK__textpk__27E3AA06' is being referenced by table 'textfk', foreign key constraint 'FK'.

  Msg 3727, Level 16, State 0, Line 2

  Could not drop constraint. See previous errors.

  you cant disable PK unless disabling FK first

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)