How do I temporarily ignore foreign key constraints during a transaction?
Posted on 2007-03-22
I have an Access front end that has been conneted to Access tables but we are converting our large sites to SQL. we run a number of transactions that insert records in multiple tables, some of which have foreign key constraints against each other. For example Insert a record in Table A with field NAME, then insert a reocrd in table B with field NAME and there is a foreign key constraint requiring NAME be in table A before inserting the reocrd in table B.
Access handles this fine inside a transaction, but SQL fails because the new record in table A is not inserted yet so it fails the insert to table B.
I hate to write my own transactions if it is avoidable. Is there a way to turn off the constraint checking (or any other simple solution) until the transaction is written and then turn it back on and verify the records so the are not marked as not trusted?