UPDATE statement conflicted with the FOREIGN KEY constraint

• daniel vella-471037

  SSC Veteran

  Points: 207

  More actions

  September 27, 2007 at 4:00 am

  #76786

  I am running the following procedure:

  ALTER PROCEDURE [dbo].[spA_FSH_InsertVslLic5]

  @IDINT=NULL,

  @CrewMaxNumberNUMERIC(2,0)=NULL,

  @CrewMinNumberNUMERIC(2,0)=NULL,

  @FullTimeFishermenNumberNUMERIC(2,0)=NULL,

  @CrewRemarksVARCHAR(30)=NULL,

  @FirstPortIDINT=NULL,

  @OperativePeriodOneVARCHAR(30)=NULL,

  @SecondPortIDINT=NULL,

  @OperativePeriodTwoVARCHAR(30)=NULL,

  @ThirdPortIDINT=NULL,

  @OperativePeriodThreeVARCHAR(30)=NULL,

  @ForthPortIDINT=NULL,

  @OperativePeriodFourVARCHAR(30)=NULL,

  @FifthPortIDINT=NULL,

  @OperativePeriodFiveVARCHAR(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!

• Jo Pattyn

  SSC-Dedicated

  Points: 32435

  More actions

  September 27, 2007 at 4:09 am

  #736655

  What is the foreign key FK_tb_vessellic_vsl_fsh_tb_lkpports_prt_fsh2?

  How does @ID get filled in, I only see @IDINT

• Grant Fritchey

  SSC Guru

  Points: 399170

  More actions

  September 27, 2007 at 6:30 am

  #736696

  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

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Tomm Carr

  SSCertifiable

  Points: 5426

  More actions

  September 28, 2007 at 11:06 am

  #737208

  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

• Todd Janes

  SSC Journeyman

  Points: 80

  More actions

  April 12, 2011 at 8:47 am

  #1310434

  Can a lack of detail be tremendous?

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 12, 2011 at 8:59 am

  #1310444

  Yes... never heard that expression b4 but it makes sens when you think about it.