[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

COLUMN FOREIGN KEY error

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
usky1
Asked:
usky1
  • 7
  • 6
1 Solution
 
_agx_Commented:
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
 
usky1Author Commented:
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
 
_agx_Commented:
> 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
_agx_Commented:
> 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
 
usky1Author Commented:
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
 
usky1Author Commented:
In the previuous if I take out all the loop and row references it will work fine for one record.
0
 
_agx_Commented:
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
 
usky1Author Commented:
what would be the proper way to pass this field if there is not an update needed?
<cfelse>
    ''
  </cfif>
0
 
_agx_Commented:
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
 
usky1Author Commented:
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
 
_agx_Commented:
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
 
usky1Author Commented:
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
 
usky1Author Commented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now