Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2007
    Posts
    283

    UPDATE statement conflicted with COLUMN REFERENCE constraint

    ms sql server 2000 sp4
    getting this error try to modify one field in a table

    [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]UPDATE statement conflicted with COLUMN REFERENCE constraint 'eq_em_id'. The conflict occurred in database 'KOC151', table 'eq', column 'em_id'.

    This worked until last week. checked for corruption in the DB and there is none.

    This is an application that security uses to keep track of employees information. They cannot modify someones name.
    can anyone help troubleshoot?
    table DDL attached
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    This is a foreign key constraint. Are they trying to update the em_id column? Let us have a look at the update statement.

  3. #3
    Join Date
    Dec 2007
    Posts
    283
    yes, the em_id column has employee names. the error occurs when they try and change an employee name

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,634
    Employee name as a foreign key? Very nice! I will always have a job!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2005
    Posts
    161
    That's the problem. The constraint says that the em_id in this table has to reference an em_id in the em table. When you change the em_id in the child table, you violate the constraint.

    As a footnote, this is not a good design. Google some articles about normalised databases. You should only store employee names once ... in a table about the employees. The child table should reference the primary key of the em table (which should not be the employee name). Primary keys should never change. You change the employee name in the em table only.

  6. #6
    Join Date
    Dec 2007
    Posts
    283
    Thanks. I didn't design the APP or the Database.. just trying to figure out how to get it working again.. They claim that it stopped working sometime last week..

  7. #7
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by rdjabarov
    Employee name as a foreign key? Very nice! I will always have a job!!!
    You could have just posted your signature as a reply

  8. #8
    Join Date
    Dec 2007
    Posts
    283
    definitely a learning experience After looking at the sql profiler trace I see what's happening.

    The conflict occurs because of the column constraint. It can't update EM_ID in the EQ table because it references the EM_ID in the EM table which is the Primary Key for that table. Both EM_ID columns contain employee names.

    Badly designed App.. and DB

  9. #9
    Join Date
    Jun 2008
    Posts
    3

    UPDATE problem Need Help

    Hi,
    I have a peculiar problem
    there are 5 tables involved
    EMP_TRNSACTION has the following columns
    Emp_ID
    EMP_CD
    Lst_CD_1
    LST_CD_2
    LST_CD_3
    LST_PCT_NR

    EMP table has

    EMP_ID
    EFF_DT
    etc

    I need to get total number of emp_id for each EMP_CD
    for example emp_cd '13A' may have 5 emp_ids corresponding to it.
    I was able to get this by doing a frequency count
    insert into temp table
    (emp_cd,
    emp_count,
    lst_cd_1,
    lst_cd_2,
    lst_cd_3,
    lst_pct_nr,
    eff_dt)
    select
    t.emp_cd,
    count(emp_id) ,
    t.lst_cd_1,
    t.lst_cd_2,
    t.lst_cd3,
    t.lst_pct_nr,
    e.eff_dt)
    from emp_trans t, emp e
    where t.emp_id = e.emp_id
    and t.lst_cd_1 = 'S'
    and t.lst_cd_2 = 'YY'
    and t.lst_cd_3 = '00'
    and t.lst_pct_nr between '10' and '20'
    and to_char(e.eff_dt,'YYYYMM') between '200701' and '200801'
    group by t.emp_cd,
    t.lst_cd_1,
    t.lst_cd_2,
    t.lst_cd3,
    t.lst_pct_nr,
    e.eff_dt
    order by emp_cd;
    This worked fine.
    Now there are 3 different tables
    Policy QUAL and RULE
    The Qual table has a total_qy field where the count(emp_id) needs to be placed.
    Policy table has
    plcy_nr
    emp_cd
    lst_cd1
    lst_cd2
    lst_cd3

    QUAL table has
    plcy_nr
    plcy_id
    total_qy -- needs to be updated from the count(emp_id)from the temp table

    Rule table has
    plcy_id
    lst_pct_lo_nr
    lst_pct_hi_nr

    I was able to create a tmp table and get all the columns needed from the
    5 tables

    when I do a update it updates all the 4000+ rows in the QUAL table instead
    of just 5
    it is difficult to join the last 3 tables because Policy table and qual table
    has Plcy_nr to join and QUAL and Rule table has plcy_id to join
    my update looks like this
    update QUAL q
    set q.total_qy =
    (select t.emp_count from temp
    where t.policy_plcy_nr = q.plcy_nr
    and t.rule_plcy_id = q.plcy_id
    and t.emp_cd = t.policy_emp_id
    and t.lst_cd_1 = 'S'
    and t.lst_cd_2 = 'YY'
    and t.lst_cd_3 = '00'
    and t.lst_pct_nr between '10' and '20'
    and to_char(t.eff_dt,'YYYYMM') between '200701' and '200801');

    when i run the update it updates all the 4018 rows
    so I gave specific criteria of emp_id = 13A
    even then it updated all the rows in the tables.
    the problem is the Qual table where the total_qy is getting updated
    does not have emp_cd or any other common field the emp_id
    is in the Policy table and even though i join the policy table and
    the QUAL table by the t.policy_plcy_nr from the temp table it does not work.
    can it be accomplished by pl sql.
    I am very new to PL SQL too.
    please help.

  10. #10
    Join Date
    Dec 2007
    Posts
    283
    i think you probably need to start your own thread..

  11. #11
    Join Date
    Jun 2008
    Posts
    3
    how do i start a new thread?

    thanks

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Go into the SQL Server forum and click "New Thread".

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by cascred
    As a footnote, this is not a good design. Google some articles about normalised databases. You should only store employee names once ... in a table about the employees.
    As a footnote to the footnote - using employee name as a primary key is poor design but not a normalisation problem.

  14. #14
    Join Date
    Jun 2008
    Posts
    3
    I am working on Oracle
    thanks

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by garuda
    how do i start a new thread?
    Quote Originally Posted by pootle flump
    Go into the SQL Server forum and click "New Thread".
    Quote Originally Posted by garuda
    I am working on Oracle
    Now - one of the key skills in programming and IT generally is problem solving. Given the information you have in front of you so far - what do you think your next move should be?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •