If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > UPDATE statement conflicted with COLUMN REFERENCE constraint

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
File Type: txt eq.txt (38.5 KB, 144 views)
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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."
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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..
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 283
i think you probably need to start your own thread..
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jun 2008
Posts: 3
how do i start a new thread?

thanks
Reply With Quote
  #12 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Go into the SQL Server forum and click "New Thread".
Reply With Quote
  #13 (permalink)  
Old
King of Understatement
 
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.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jun 2008
Posts: 3
I am working on Oracle
thanks
Reply With Quote
  #15 (permalink)  
Old
King of Understatement
 
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On