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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy