December 13, 2010 at 3:28 pm
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?
December 13, 2010 at 3:40 pm
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.
December 13, 2010 at 3:48 pm
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.
December 13, 2010 at 10:50 pm
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;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply