February 9, 2012 at 1:55 pm
Is there a way around this restriction "Destination table cannot reference the source table using FOREIGN KEY constraint." #15 from http://sqlserverpedia.com/wiki/Switching_Partitions_-_Prerequisites
when doing a switch without disabling constraints?
For example if I run ALTER TABLE [mytable].[attribute] NOCHECK CONSTRAINT [FK_mykey]
run my switch...
and then
ALTER TABLE [mytable].[attribute] ADD CHECK CONSTRAINT [FK_mykey]
If looking at Foreign Key Relationships, I end up with FK_mykey having option "Check Existing Data On Creation Or Re-Enabling" set to No whereas before running the scrip above it was Yes.
Thanks
February 9, 2012 at 2:01 pm
No. No workaround.
I would drop the offending FKs, set them back after the partition switch.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 9, 2012 at 2:56 pm
PaulB-TheOneAndOnly (2/9/2012)
No. No workaround.I would drop the offending FKs, set them back after the partition switch.
So having "Check Existing Data On Creation Or Re-Enabling" set to No won't really affect integrity of the data since the foreign key was created with that option Yes inititally. Is that correct?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply