Solved

COLUMN FOREIGN KEY error

Posted on 2007-11-14
13
160 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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