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!
please share your query
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.
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?
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.