August 13, 2013 at 7:28 am
Hello
I work at a place with many non-technical project managers and an external vendor. Each week the vendor sends a .sql file containing code to update their database which I run for them. My boss wants this automated. The first problem is that the T-SQL code often generates referential constraint errors. The vendor solution is to rerun the script.
When I did a data transfer in the past and received a referential constraint, (copying records from a table in a test database to a table in production database) I dropped the constraints, copied the records over using SSMS, then recreated the constraints.
I asked the vendor if he could look into having the constraints dropped-run his code-then recreate the constraints. The vendor replied saying that changing the table structure will casue problems down the road. The project managers go along with whatever the vendor says.
Are there dangers with dropping constraints, running an insert statement, then adding the constraint again?
This is an error message I get...
Msg 547, Level 16, State 0, Line 382
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblObjectTemplateMenuItemLink_tblObjectTemplateRelationship". The conflict occurred in
database "Intelligrants_Test", table "dbo.tblObjectTemplateRelationship", column 'otlID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 390
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblObjectTemplateMenuItemLink_tblObjectTemplateRelationship". The conflict occurred in
database "Intelligrants_Test", table "dbo.tblObjectTemplateRelationship", column 'otlID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 398
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblObjectTemplateMenuItemLink_tblObjectTemplateRelationship". The conflict occurred in database "Intelligrants_Test", table "dbo.tblObjectTemplateRelationship", column 'otlID'.
The statement has been terminated.
Caused by...
INSERT INTO tblObjectTemplateMenuItemLink(omlID,omlObjectTemplateRelationship_otlID,omlMenuItemA_omnID,omlMenuItemB_omnID,omlRequireSave,omlSystem_sysID)
VALUES (15099,3997,89244,76566,1,223)
I suggested...
USE [Intelligrants_Test]
GO
ALTER TABLE [dbo].[tblObjectTemplateRelationship] DROP CONSTRAINT [DF_tblObjectTemplateLink_otlCanCreateBFromA]
GO
ALTER TABLE [dbo].[tblObjectTemplateRelationship] DROP CONSTRAINT [DF_tblObjectTemplateRelationship_otlEnableCopy]
GO
INSERT INTO tblObjectTemplateMenuItemLink(omlID,omlObjectTemplateRelationship_otlID,omlMenuItemA_omnID,omlMenuItemB_omnID,omlRequireSave,omlSystem_sysID)
VALUES (15099,3997,89244,76566,1,223)
ALTER TABLE [dbo].[tblObjectTemplateRelationship] ADD CONSTRAINT [DF_tblObjectTemplateLink_otlCanCreateBFromA] DEFAULT (0) FOR [otlCanCreateBFromA]
GO
ALTER TABLE [dbo].[tblObjectTemplateRelationship] ADD CONSTRAINT [DF_tblObjectTemplateRelationship_otlEnableCopy] DEFAULT (0) FOR [otlEnableCopy]
GO
When we get an error, the vendor says to run the script again. Sometimes this works - sometimes I think they remove records from the table to avoid the errors, I don't know.
Is there a way to avoid referential constraint errors? Is dropping the constraints the only way? Is there danger with dropping constraints? If I drop the constraints and insert a record, what if that record doesn't agree with the constraint?
Thanks
Dave
August 13, 2013 at 7:37 am
Are there dangers with dropping constraints, running an insert statement, then adding the constraint again?
Yes. If you're doing this because the data you want to insert violates the constraints, you're not going to be able to add the constraints back on at the end again. You'll get an error message telling you that the constraint can't be created because there's already data that violates it.
Constraints are there for a reason. They shouldn't be ignored because they're inconvenient. You should ask your vendor instead to include an existence test in the script. They could use the MERGE statement instead, but only on SQL Server 2008 or later.
John
August 13, 2013 at 7:58 am
Thanks John - I only had one situation where I tried importing records, got the constraint error, dropped the constraints, added the records then recreated the constraints - I don't know why I didn't get an error that time. They are using 2005. I will look into the T-SQL for checking for existance. Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply