Solved

COLUMN FOREIGN KEY error

Posted on 2007-11-14
13
161 Views
Last Modified: 2013-12-24
I am trying to update a column in my database. when I do an individual update to one record it works great, but when I try to update multiple rows I get the following error,
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Cats_Prods_FK1'. The conflict occurred in database 'ACB01', table 'Cats', column 'CatID'.
0
Comment
Question by:usky1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 20282317
I'm not sure why you're updating a PK but here is a good explanation of that error
http://www.sql-server-helper.com/error-messages/msg-547-update.aspx
0
 

Author Comment

by:usky1
ID: 20282562
The PK is ion the table CATS, column CatID and I am updating the table Prods, column CatID. This is new to me so I'm somewhat confused. If I update an individual record for the CatID it works, it is just with the multiple table routine that I get this error. I also do not have any control over the database structure. Is there a way for me to set the cascade in a SQL statement or can it only be done with the creation of the table?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20282754
> I am updating the table Prods, column CatID

I would expect that error to occur if you were updating the PK of the "Cats" table (not Prods).  As long as you're setting Prods.CatID = an _existing_  PK value in the "Cats" table, there shouldn't be a problem.  
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 52

Expert Comment

by:_agx_
ID: 20282990
> Is there a way for me to set the cascade in a SQL statement or can it only be
> done with the creation of the table?

Yes,  assuming you understand the implications, you could use ALTER TABLE.  But .. the error message suggests you may be updating the wrong table:  ie you're updating "Cats" instead of "Prods"

0
 

Author Comment

by:usky1
ID: 20283091
Here are the parts of my page that relate to the update and  there is no reference to updating Cats.
So I'm still confused.


-----------------query--------------------
<cfquery name="Recordset1" datasource="#request.dataSource#">
SELECT Prods.ProdID, Prods.CatID
FROM Prods
</cfquery>

------------update statement---------------------
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
<cfloop index="idx" from="#form.startCount#" to="#form.endCount#">
  <cfquery datasource="#request.dataSource#">
    UPDATE Prods
    SET CatID=
  <cfif IsDefined("FORM.CatID" & idx) AND FORM["CatID" & idx] NEQ "">
    <cfqueryparam value="#Trim(Evaluate("FORM.CatID" & idx))#" cfsqltype="cf_sql_numeric">
    <cfelse>
    ''
  </cfif>
    WHERE ProdID=
      <cfqueryparam value="#Trim(Evaluate("form.ProdID" & idx))#" cfsqltype="cf_sql_numeric">
  </cfquery>
  </cfloop>
</cfif>

------------------form-----------------------------------------
<form method="post" name="form1" action="">
<cfoutput>  <tr>
    <td width="161" bgcolor="##CCCCCC"><div align="center"><span class="style4">ProdID</span></div></td>
    <td width="171"><div align="center"><span class="style4">CatID</span></div></td>
    <td width="209">&nbsp;</td>
  </tr>
</cfoutput>
  <cfoutput query="Recordset1" startRow="#StartRow_Recordset1#" maxRows="#MaxRows_Recordset1#">
    <tr>
      <td bgcolor="##CCCCCC"><div align="center"><a href="catupd.cfm?recordID=#Recordset1.ProdID#" class="style5">#Recordset1.ProdID# </a></div>
        <input name="ProdID#CurrentRow#" type="hidden" id="ProdID#CurrentRow#" value="#recordset1.ProdID#" />
</div></td>
      <td><div align="center"><span class="style5">#Recordset1.CatID# </span></div></td>
      <td><cfset request.CatID_Value = #Recordset1.CatID#>
          <cfmodule template="#request.DefaultAppMapping#/components/tree/CategorySelect_pro.cfm" type="list" sector="products" multiple="no" attribute_name="CatID#CurrentRow#"></td>
    </tr>
  </cfoutput>
  <tr><td>&nbsp;</td><td><div align="center" class="style9">
            <input name="" type="submit" value="Update">
                  <input name="" type="reset" value="Reset">
          </div></td>
      <td>&nbsp;</td></tr>
        <input type="hidden" name="ProdID" value="#Recordset1.ProdID#">
      <input type="hidden" name="MM_UpdateRecord" value="form1">
        <cfoutput>
      <input name="startCount" type="hidden" value="#StartRow_Recordset1#" />
      <input name="endCount" type="hidden" value="#EndRow_Recordset1#" />
            </cfoutput>
    </form>
0
 

Author Comment

by:usky1
ID: 20283102
In the previuous if I take out all the loop and row references it will work fine for one record.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20283161
By any chance is the "CatID" missing/blank  when this error occurs?

You can't set CatID equal to an empty string ''.  That column is defined as a FK. That means it can only contain an existing ID from the "Cats" table.  An empty string '' isn't likely to be an ID in that table.

<!--- wrong --->
SET CatID=
  <cfif IsDefined("FORM.CatID" & idx) AND FORM["CatID" & idx] NEQ "">
    <cfqueryparam value="#Trim(Evaluate("FORM.CatID" & idx))#" cfsqltype="cf_sql_numeric">
    <cfelse>
    ''
  </cfif>
0
 

Author Comment

by:usky1
ID: 20283205
what would be the proper way to pass this field if there is not an update needed?
<cfelse>
    ''
  </cfif>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20283236
Since in this case, you're only updating one column: CatID.  Skip the update altogether if its not defined.

Also, are you sure you're defining the CatID field? I see where product ID is defined, but not CatID.  
   <input name="ProdID#CurrentRow#" ...

Perhaps its in your cfmodule code..
0
 

Author Comment

by:usky1
ID: 20284016
Since in this case, you're only updating one column: I will be adding additional columns in.

 I actually have 10 other columns in this routine, which works fine. This column was giving me problems so I broke it out by itself to troubleshoot.

The CatID is being defined in the cfmodule. Any other suggestions?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20284121
Then omit the column from the UPDATE if the value isn't defined or is empty

Another option is to set the column equal to itself if the variable is undefined or empty.   Not ideal, but it should work.

SET CatID=
  <cfif IsDefined("FORM.CatID" & idx) AND FORM["CatID" & idx] NEQ "">
       <cfqueryparam value="#Trim(Evaluate("FORM.CatID" & idx))#" cfsqltype="cf_sql_numeric">
    <!--- notice CatID is not a variable. its a column name --->
    <cfelse>
        CatID
  </cfif>

If the field were always defined you could also use

<!--- the value must be defined --->
SET CatID =   COALESCE(
                       <cfqueryparam value="#Trim(FORM['CatID' & idx])#" cfsqltype="cf_sql_numeric"
                               null="#not len(Trim(FORM['CatID' & idx]))#"
                             >
                        ,   CatID )


0
 

Author Comment

by:usky1
ID: 20289374
I tried the,
SET CatID=
  <cfif IsDefined("FORM.CatID" & idx) AND FORM["CatID" & idx] NEQ "">
       <cfqueryparam value="#Trim(Evaluate("FORM.CatID" & idx))#" cfsqltype="cf_sql_numeric">
    <!--- notice CatID is not a variable. its a column name --->
    <cfelse>
        CatID
  </cfif>
No errors but no update either.



0
 

Author Comment

by:usky1
ID: 20290440
I took away the cfmodule and rewrote the cfselect to below. I added an additional query, catQuery, and it is now working.

<cfselect
                  name="CatID#CurrentRow#"
                  display="caption"
                  query="catQuery"
                  required="yes"
                  message="Please select a category"
                  selected="#GetProds.catID#"
                  value="catID"
                  >
            </cfselect>
0

Featured Post

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
WSUS Client Issues 52 2,490
Configure a Local PHP Interpreter for Phpstorm 2 432
Why is my Splunk Web URL not working? 2 85
Help with a redirect in web.config file 8 65
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
What You Need to Know when Searching for a Webhost Provider
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question