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

Referential Integrity - how to avoid errors so I can automate manual process - vendor code Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 7:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:00 PM
Points: 87, Visits: 546
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
Post #1483727
Posted Tuesday, August 13, 2013 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 5,488, Visits: 10,339
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
Post #1483733
Posted Tuesday, August 13, 2013 7:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:00 PM
Points: 87, Visits: 546
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.
Post #1483747
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse