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

This is a foreign key constraint. Are they trying to update the em_id column? Let us have a look at the update statement.

yes, the em_id column has employee names. the error occurs when they try and change an employee name

Employee name as a foreign key? Very nice! I will always have a job!!!

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.

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..

You could have just posted your signature as a reply

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

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****le_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.

i think you probably need to start your own thread..

how do i start a new thread?

thanks

Go into the SQL Server forum and click "New Thread".

As a footnote to the footnote - using employee name as a primary key is poor design but not a normalisation problem.

I am working on Oracle
thanks

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?