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

UPDATE statement conflicted with the FOREIGN KEY constraint Expand / Collapse
Author
Message
Posted Thursday, September 27, 2007 4:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 16, 2012 3:54 AM
Points: 1, Visits: 8
I am running the following procedure:

ALTER PROCEDURE [dbo].[spA_FSH_InsertVslLic5]
@ID INT=NULL,
@CrewMaxNumber NUMERIC(2,0)=NULL,
@CrewMinNumber NUMERIC(2,0)=NULL,
@FullTimeFishermenNumber NUMERIC(2,0)=NULL,
@CrewRemarks VARCHAR(30)=NULL,
@FirstPortID INT=NULL,
@OperativePeriodOne VARCHAR(30)=NULL,
@SecondPortID INT=NULL,
@OperativePeriodTwo VARCHAR(30)=NULL,
@ThirdPortID INT=NULL,
@OperativePeriodThree VARCHAR(30)=NULL,
@ForthPortID INT=NULL,
@OperativePeriodFour VARCHAR(30)=NULL,
@FifthPortID INT=NULL,
@OperativePeriodFive VARCHAR(30)=NULL
AS
BEGIN
BEGIN TRY
UPDATE tb_vessellic_vsl_fsh
SET
vsl_CrewMaxNumber = @CrewMaxNumber,
vsl_CrewMinNumber = @CrewMinNumber,
vsl_FullTimeFishermenNumber = @FullTimeFishermenNumber,
vsl_CrewRemarks = @CrewRemarks,
vsl_prt_FirstPortID_fk = @FirstPortID,
vsl_OperativePeriodOne = @OperativePeriodOne,
vsl_prt_SecondPortID_fk = @SecondPortID,
vsl_OperativePeriodTwo = @OperativePeriodTwo,
vsl_prt_ThirdPortID_fk = @ThirdPortID,
vsl_OperativePeriodThree = @OperativePeriodThree,
vsl_prt_FourthPortID_fk = @ForthPortID,
vsl_OperativePeriodFour = @OperativePeriodFour,
vsl_prt_FifthPortID_fk = @FifthPortID,
vsl_OperativePeriodFive = @OperativePeriodFive
WHERE vsl_ID_pk = @ID
END TRY
BEGIN CATCH
EXECUTE spA_GEN_LogError
END CATCH

END


but every time getting the error and my data not inserted into the database:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_tb_vessellic_vsl_fsh_tb_lkpports_prt_fsh2". The conflict occurred in database "db_fsh_MRAEFish", table "dbo.tb_lkpports_prt_fsh", column 'ID'.

Any ideas on this?? Thank you!
Post #403390
Posted Thursday, September 27, 2007 4:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 1,395, Visits: 6,608
What is the foreign key FK_tb_vessellic_vsl_fsh_tb_lkpports_prt_fsh2?

How does @ID get filled in, I only see @IDINT
Post #403392
Posted Thursday, September 27, 2007 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
Sure sounds like you have a foreign key constrain violation. Check the data incoming against the data in the table that the error indicates. You have to have a discrepancy between the two.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #403443
Posted Friday, September 28, 2007 11:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:47 PM
Points: 501, Visits: 784
You have supplied us a tremendous lack of detail to supply a definitive answer, but thanks to your somewhat convoluted naming convention, I can make a guess. There are five columns among all the ones being updated, named "vsl_prt_{First|Second|Third|Fourth|Fifth}PortID_fk". I'm guessing these are foreign keys to the tb_lkpports_prt_fsh table. Any of the new values being updated here must already exist in the "id" column of that table -- or be null. Examine those values and check against the tb_lkpports_prt_fsh table.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Post #404152
Posted Tuesday, April 12, 2011 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 2, 2011 2:14 PM
Points: 2, Visits: 45
Can a lack of detail be tremendous?
Post #1092144
Posted Tuesday, April 12, 2011 8:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Yes... never heard that expression b4 but it makes sens when you think about it.
Post #1092155
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse