Solved

COLUMN FOREIGN KEY error

Posted on 2007-11-14
13
162 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
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

726 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