Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

COLUMN FOREIGN KEY error

Posted on 2007-11-14
13
Medium Priority
?
164 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
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

618 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