Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

disable primary key constraints in table Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 3:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 16, 2013 11:15 AM
Points: 32, Visits: 109
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?
Post #1034109
Posted Monday, December 13, 2010 3:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:11 PM
Points: 1,752, Visits: 1,324
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.
Post #1034112
Posted Monday, December 13, 2010 3:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 16, 2013 11:15 AM
Points: 32, Visits: 109
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.
Post #1034118
Posted Monday, December 13, 2010 10:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1034194
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse