locked
update statement conflicted with column foreign key error RRS feed

  • Question

  • i have an update query that works fine in my development and uat environment but when i move it to production i get this error:

    Msg 547, Level 16, State 0, Line 1

    UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'cmfk_cmpny_persn_cmpny_id'. The conflict occurred in database 'saba_prod_tp2', table 'TPT_COMPANY', column 'id'.

    The statement has been terminated.

    (0 row(s) affected)


    all 3 environments are identical, here is the query:

    update

     

    S

    set

    S

    .company_id = C.Org_ID from saba_prod_tp2.saba_uat_tp2.cmt_person S inner join dbo.organizationlocationemployee C on REPLACE(UPPER(c.Emp_NTAccount),'NET\','') = username and s.company_id = 'bisut000000000001000'

     

     


    the table being referred to in the query TPT_company isnt used by the view, however cmt_person is part of the view and uses the referenced as a key...but like i mentioned this works in my 2 other environments
    thank you,

    Monday, September 14, 2009 7:32 PM

Answers

  • A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table
    in your case table cmt_person  contains the primary key of 'TPT_COMPANY' table as a foriegn key

    primary purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table so your FOREIGN KEY constraint 'cmfk_cmpny_persn_cmpny_id' is controling the data in the cmt_person table i.e. if you want to insert or update a company id cmt_person table  table that company id must exists in the tpt_company table.


    you have verified  cmt_person and  tpt_company table but you have not checked organizationlocationemployee table. please verify this table also because you are updating from organizationlocationemployee and in this table you have some org_id's  which you are moving in the cmt_person table and those id's would not exists in tpt_company  table.

    Please ask if still you have confusion

    Best Regards
    Arif

    • Marked as answer by Zongqing Li Monday, September 21, 2009 6:38 AM
    Tuesday, September 15, 2009 4:15 AM

All replies

  • table cmt_person  contains the primary key of 'TPT_COMPANY' table as a foriegn key and you are trying to update the same key that is company_id which has no entry in its parent table.

    you can add a where clause in the end of the query

    where C.Org_ID in (select id from  TPT_COMPANY )

    after that you can replace IN with inner join or exsists to optimize the query
    Monday, September 14, 2009 7:44 PM
  • When you say identical, do you have database mirroring in place to synchronize? Do you have the same triggers and constraints on your development as your production?


    AE, PMP, MCTS
    Monday, September 14, 2009 7:47 PM
  • hello, i do not have db mirroring in place, i have restored the prod database on the other 2 environments.
    Monday, September 14, 2009 7:52 PM
  • hi Arif,
    why would this work in my other 2 environments. i have checked both the cmt_person table and tpt_company table and they match the tables in all 3 environments. same contraints, pk, triggers...
    Monday, September 14, 2009 7:54 PM
  • A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table
    in your case table cmt_person  contains the primary key of 'TPT_COMPANY' table as a foriegn key

    primary purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table so your FOREIGN KEY constraint 'cmfk_cmpny_persn_cmpny_id' is controling the data in the cmt_person table i.e. if you want to insert or update a company id cmt_person table  table that company id must exists in the tpt_company table.


    you have verified  cmt_person and  tpt_company table but you have not checked organizationlocationemployee table. please verify this table also because you are updating from organizationlocationemployee and in this table you have some org_id's  which you are moving in the cmt_person table and those id's would not exists in tpt_company  table.

    Please ask if still you have confusion

    Best Regards
    Arif

    • Marked as answer by Zongqing Li Monday, September 21, 2009 6:38 AM
    Tuesday, September 15, 2009 4:15 AM