Insert Unsuccesful

[anthonyjpv]

I have set my table to InnoDB

which has parent to child relation

table 2(child) has FK Reference to table 1 (parent) PK

table 2 has cascade on update and delete

I am trying to insert a record in table 2 which PK doesnt exist yet in table 1

my goal is to create a temporary fk in table 2 and then update that later to a valid FK once PK has been created in table 1

thanks!

[baig772]

please share your query

[pritaeas]

I am trying to insert a record in table 2 which PK doesnt exist yet in table 1

This is precisely what FK's are meant to prevent. I am not sure if MySQL INNODB tables have/support nullable FK's, but that's what you need.

[anthonyjpv]

yes youre right pritaeas! im looking to see if thats possible. it says on ibm website http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0020153.htmlthat i need an "INSERT RULE" i dont know what that means by looking online and on dev docs

I have to agree with Pritaeas and you see it yourself. FKs by definition should have a PK counterpart. MySQL isn't too fussy about creating 'constraints' (FKs), but the fact that you have coupled the tables with a cascade makes using a temporary FK unsafe (which it probably would be anyway)/impossible.

I can't see how you'd end up in this situation. You perhaps need to insert a new PK into table 1, which may hold temporary data, the user can then come back to edit BEFORE entering data in table2.

Is this a system similar to where a basket may be filled with items before the user logs in or registers?

[pritaeas]

You may want to read this SO thread on the subject.