October 7, 2017 at 1:55 pm
I have a puzzle here.
The initial problem is trying to import data from csv file and I haven't been able to get the date formats to match up/convert.
So, I decided to drop the table into which I will import the data. To do so, I need to drop the FK constraints.
I decided to script the drop and create table statements into the query window, so I can recreate the table as I test different data types and how FK, nulls affect importation.
The ALTER TABLE...DROP CONSTRAINT works if I use the one that was created by the SSMS. However, I also need to drop several constraints in related tables, so I manually type out the exact same statements using the correct FK names, of course. I get an error message stating that that field is NOT a constraint. Ok, so i script out that related table as well, and when I execute that one line of code (ALTER TABLE...DROP CONSTRAINT) it works perfectly!
What is the issue here?
The script goes like this:ALTER TABLE [dbo].[Assets] DROP CONSTRAINT [FK__Assets__WorkDesc__5165187F]
GO
That works, but if I type the exact same thing, adjusting the characters at the end for the correct name, it does not work.
thoughts?
October 8, 2017 at 10:31 am
When you say "it does not work", what do you mean? Are you presented with an error? Does the Constraint no actually drop? Does the SQL Server crash?
Personally, however, I have to ask; Why do you want to drop the Foreign Constraints? Foreign Constraints are there for a reason, to help maintain referential integrity. Dropping the constraint means that this can't be maintained and therefore any foreign keys you have cannot be trusted by SQL Server (effectively rendering them pointless).
Perhaps, rather than asking why can't you drop the Foreign Key and Constraints, you should be asking why do you want to and what can you do instead to ensure that the data meets the required constraints. Then you don't need to drop your Constraints and the data can be trusted. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 9, 2017 at 8:32 am
Don't touch the existing Asset table, leave it and constraints intact. Once you've figured out the datetime conversion issue, you can adjust the constraints if needed before doing your final load.
On a separate note, I always advocate to explicitly name FK constraints. More than once I've had deployment scripts fail because of system named constraints.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply