JasonC
|
| Posted: 09/16/2005, 10:15 AM |
|
How come when I hit the submit button codecharge tries to use a insert instead of update sql statement
Invalid SQL: INSERT INTO corp_employee_position_tracking (employee_id,position_id,date_changed)VALUES ('1552-500','','2005-09-16')
MySQL Error: 1216 (Cannot add or update a child row: a foreign key constraint fails)
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 09/16/2005, 10:31 AM |
|
I suspect that CodeCharge does what you tell it to do 
So the above SQL might be some custom code added after the update, or the "Submit" button is configured to perform an insert instead of update.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
DonB
|
| Posted: 09/16/2005, 10:46 AM |
|
Are you sure you should be doing an Update or did you make that comment
based on the error message?
The reported error here is because (probably) position_id is empty. I
suspect THAT is the Foreign Key being complained about (because the table
employee_position_tracking has it as NOT NULL. I would guess that is
because it's part of the Primary Key of the table.
--
DonB
http://www.gotodon.com/ccbth
"JasonC" <JasonC@forum.codecharge> wrote in message
news:2432afdc340d50@news.codecharge.com...
> How come when I hit the submit button codecharge tries to use a insert
instead
> of update sql statement
>
> Invalid SQL: INSERT INTO corp_employee_position_tracking
> (employee_id,position_id,date_changed)VALUES ('1552-500','','2005-09-16')
> MySQL Error: 1216 (Cannot add or update a child row: a foreign key
constraint
> fails)
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
JasonC
|
| Posted: 09/16/2005, 11:02 AM |
|
Peter
Well I have checked the operation before i wrote this and just did again to make sure. It is set to update. I have had this problem in the past which is strange. I have updated info fine, if I try to edit the same record again right away it would give me an error similar about duplicate records and trying an insert instead of an update. Note: there is no custom code on this page
Don
I am trying to change the employee_id field. If I change say the supervisor it updates fine. Why is it not passing the info, it is just trying insert employee_id,position_id,date_changed and why insert? Yes the primary key is those three fields. I hope I am making some sense - thanx for any help u can give me.
|
|
|
|
DonB
|
| Posted: 09/16/2005, 2:23 PM |
|
Whether an INSERT or UPDATE is performed is based on the absence or presence
of primary key values. Since you have omitted one (position_id is empty),
that's probably why it's doing an INSERT. CCS evaluates the parameters
supplied and figures out which operation should be performed.
The FK constraint error has to be getting raised because 'position_id' isn't
supplied.
If the idea is this employee has no supervisor, then there ought to be a
position_id that indicates that, rather than leaving it blank or NULL -
since it's part of the PK.
The detailed error message for 1216 is:
Error: 1216 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW)
which convinces me that it's occurring because you are not referencing a row
with the position_id = ''.
Lastly, most DBMS will disallow UPDATEs to the PK, as it eventually assures
you will screw up referential integrity. MySQL is relaxed in this behavior.
Still, it's a bad practice even if the DBMS let's you do it.
What *might* resolve this problem is to put in a Before Insert to delete the
current row before the insert executes. Thus you'd always be inserting a
PK, never updating it.
--
DonB
http://www.gotodon.com/ccbth
"JasonC" <JasonC@forum.codecharge> wrote in message
news:2432b08d36302e@news.codecharge.com...
> Peter
>
> Well I have checked the operation before i wrote this and just did again
to
> make sure. It is set to update. I have had this problem in the past which
is
> strange. I have updated info fine, if I try to edit the same record again
right
> away it would give me an error similar about duplicate records and trying
an
> insert instead of an update. Note: there is no custom code on this page
>
> Don
>
> I am trying to change the employee_id field. If I change say the
supervisor it
> updates fine. Why is it not passing the info, it is just trying insert
> employee_id,position_id,date_changed and why insert? Yes the primary key
is
> those three fields. I hope I am making some sense - thanx for any help u
can
> give me.
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
JasonC
|
| Posted: 09/19/2005, 5:56 AM |
|
Yes I totally agree that what I am trying to do is NO GOOD. Changing the PK, unfortunately when I designed the DB I didn't think that this company would be changing employee numbers. But the have different plants and if someone moves to a different location they are assigned a new employee number. Do you have a suggestion as to the best/easiest way to fix this opps on my part? Also, if I understand you correctly CCS looks for a record to decide if it is an update. So by changing the PK for the search of course it is finding none and assuming I am adding new correct? But if that is the case why would it still not pass the position_id?
Thanx again for your quick and GREAT responses!
Jason C
|
|
|
|
Walter Kempees
|
| Posted: 09/19/2005, 6:38 AM |
|
Hi Jason.
Take it you have not been changing numbers yet.
Reading the thread I would suggest to
.. alter the table and add an "employee_nr"
.. update the employee_nr to contain the value of your employee_id
you will then have two fields in the table containing the same value
next:
whenever you were referring to employee_id from now on reffer to
employee_nr.
Now you can change as much as you like.
The employee_id field is from then on the table id a unique, autoincrement
primary key.
Insert update and delete will refer to emplyee_id.
Your users will identify employees by the employee_nr, a mere editable
attribute.
DonB is totally right each table should have 1 id with a never changing
value, autinc, primary unique.
Preferrably a user should never see this value, meaning should never be
bothered with it.
Bye, GoodLuck
Walter
"JasonC" <JasonC@forum.codecharge> schreef in bericht
news:2432eb5639632b@news.codecharge.com...
> Yes I totally agree that what I am trying to do is NO GOOD. Changing the
> PK,
> unfortunately when I designed the DB I didn't think that this company
> would be
> changing employee numbers. But the have different plants and if someone
> moves
> to a different location they are assigned a new employee number. Do you
> have a
> suggestion as to the best/easiest way to fix this opps on my part? Also,
> if I
> understand you correctly CCS looks for a record to decide if it is an
> update.
> So by changing the PK for the search of course it is finding none and
> assuming
> I am adding new correct? But if that is the case why would it still not
> pass
> the position_id?
>
> Thanx again for your quick and GREAT responses!
>
> Jason C
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
JasonC
|
| Posted: 09/19/2005, 6:54 AM |
|
Great idea, glad that there are smart people out there unlike me!
Thanx again. I will try that as a solution!
|
|
|
|
|
