Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Referential Integrity - how to avoid errors so I can automate manual process - vendor code


Referential Integrity - how to avoid errors so I can automate manual process - vendor code

Author
Message
NJDave
NJDave
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 598
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
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7420 Visits: 15114
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
NJDave
NJDave
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 598
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search