Error: Cannot update or add a child row: a foreign key constraint fails in Clarion

questions
#1

So basically I have two tables

Table 1 - groups

Name Type Extra
group_ID(PK) int (11) AUTO_INCREMENT
group_name varchar(30)
member_count int(11)

Table 2 - members

Name Type Extra
member_ID(PK) int(11) AUTO_INCREMENT
member_name varchar(20)
member_surname varchar(20)
member_phonenum varchar(12)
group_ID(FK) int(11)

And in phpmyadmin it all works good when I try to input data, but I run into errors whenever I try to add new data into second table through my Clarion app. Whenever I try to insert data I get the following message:

Attemtps to automatically number this record have failed.
File: members
Key: mem:PK_member_ID
Error: Cannot add or update a child row: a foreign key constraint fails ('dbusername','members',CONSTRAINT 'FK_group_ID' FOREIGN KEY('group_ID')REFRENCES 'groups'('group_ID') ON UPDATE CASCADE)(S1000).

I’ve tried everything I could think of only way I managed to work around it is by removing the auto increment but that kinda defeats the whole purpose of the ID so doesen’t really work for me.

#2

OK The problem is your auto Increment key in Clarion. The way that works is Clarion inserts a placeholder record with the auto-inc value and the rest is blank. It’s that rest being blank thats stuffing you up.
Now you might be able to put the groupID in there in the ABC primeAutoInc (I think) before parent
Other than that you need to change your methodology of adding

1 Like
#3

Thanks for the info, but do you mind pointing me in which direction should I head to do that, I’m kinda new to all of this and don’t really know what to look for, I’ve been trying all sorts of thing all morning and couldn’t figure it out.

#4

Check out the help topics “Server Side Auto incrementing” and the AutoInc and PreAutoInc.
There is code there using LAST_INSERT_ID() for MySQL.

1 Like
#5

On your members update form, go to the embeds.
Expand ThisWindow and look for PrimeFields
try Adding your code to assign the groupID there. I’m not sure if you should add before or after parent, but a little experimenting should work it out :slight_smile: