Solved

How do I temporarily ignore foreign key constraints during a transaction?

Posted on 2007-03-22
12
5,149 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:KMosher
  • 6
  • 3
  • 3
12 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18776196
you can drop the foreign key constraint, and then after inserting the data, create it again with  WITH CHECK:
So, first drop the constraint:
ALTER TABLE myTable DROP CONSTRAINT myCOnstraint

Insert the data

And put the constraint back in place:

ALTER TABLE t2 WITH CHECK ADD CONSTRAINT fk_t2_t1 FOREIGN KEY(col1)
REFERENCES t1(col1)

Or you could add the data in the other order, so the constraint is not violated.
0
 
LVL 2

Author Comment

by:KMosher
ID: 18777724
I do not want to drop the constraint as other users may be adding records to this table that DO need to be cheecked. I just want to ignore the constraint for this transaction.
 Also, they ARE being added in the correct order, but, as I understand it, since they are inside a transaction SQL does not commit the first table records until it has verified that it can DO everything in the transaction. Since table A's records are not yet written it doesn't see them when it checks constraints on table B so it says it can't write them and rolls back the whole transaction.
The only way around this that I know is to break up the transaction. Write table A, then write B and if B fails then go back and delete A. The problem is that the reality of what I  am doing is quite a bit more complex. 10 or 15 tables can be involved and multiple foreign key constraints on several different tables. I would have to be prepared with 10 or 15 DELETE queries if this thing fails anywhere along the line and keep track of what needed to be deleted. I was looking for a way to just let SQL handle it.
0
 
LVL 2

Author Comment

by:KMosher
ID: 18777737
Also There might be a couple of hundred thousand records in some of the tables. Wouldn't droppping, and more importantly, putting back the constraint, be slow?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 18777931
The problem is that you have too many transactions.  Let's follow your example where B is dependent on A.  When A is inserted it can be "seen" within the SAME transaction and does not need to be committed before B can be inserted within that transaction.  But OTHER transactions cannot see A until it is committed.

The solution is to do all the related updates in the proper order and within the same transaction.  You only commit after everything is done and if any of it rolls back, it all rolls back.  

Your hesitance to turn off RI is right on the mark. It is expensive to turn back on and leaves your data vulnerable while it is off.




   
0
 
LVL 2

Author Comment

by:KMosher
ID: 18782082
dqmq:

 No, I have only one transaction. It works with the Access tables, but not SQL.
0
 
LVL 11

Expert Comment

by:dready
ID: 18783477
As you said that you converting the database, i assumed that it was only the initial copying of the data during a period that the database is not in production. But if it is done on a server that is being worked on by others, i agree with dgmg and wouldn't remove the constraint.

But if you add the records in the right order in the transaction, that should work imo.  So i would first investigate wether it really is in the right order. As you said that it is a complex procedure, you might have overlooked something?

Sorry, dont really have further ideas without knowing more about the exact db-structure and proc.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Expert Comment

by:dqmq
ID: 18783589
I am absolutely positive that it is NOT necessary to commit the parent insert before inserting the child if the inserts are done in the same transaction.  I am equally sure that the commit is necessary if the inserts occur in separate transactions.  Of course, I cannot be certain without looking at your code, but your symptoms point pretty strongly to a multiple transaction problem.  

How do you know you only have 1 transaction?  

By default, SQL Server treats individual commands as separate transactions.  So, unless you've done the extra work to explicitly issue the BEGIN TRANSACTION command on your SQL Server connection and unless all of your inserts are using the same SQL Server connection, then you probably have multiple transactions.

Here's an idea, try this.  Issue the SQL server command SET IMPLICIT_TRANSACTIONS OFF before starting your insert sequence and see if that fixes the problem.

0
 
LVL 2

Author Comment

by:KMosher
ID: 18784002
Thanks for sticking with me on this guys. Yes, I am using an explicit transaction. Here is the code that works and he code that doesn't. What fails is MulitJobsCreateKitRecordsQry Which has a relationship with  the records created in MultiJobsCreateReleaseQry.

Note that if I run the code, but skip MulitJobsCreateKitRecordsQry I can then immediately run MulitJobsCreateKitRecordsQry manually and it will create the records just fine.

Also note that it fails on
     Qry.Execute dbFailOnError Or dbSeeChanges
NOT on
    WS.CommitTrans

This code works:


Set WS = DBEngine.Workspaces(0)  '3/22/07 - Kim SQL Fixes - Must create jobs first or kit will fail
WS.BeginTrans: InTrans = True
    'Create Jobs Records
    TransMsg = "Jobs Records"
    Set Qry = DB.QueryDefs![MultiJobsCreateJobsQry]
    Qry![TopJob] = ""  '7/6/05 - Kim Added parameter really used by the multipart version
    Qry.Execute dbFailOnError Or dbSeeChanges
    JobCntr = Qry.RecordsAffected
   
     'Create Jobship Records
    TransMsg = "Jobship Records"
    Set Qry = DB.QueryDefs![MultiJobsCreateShipQry]
    Qry.Execute dbFailOnError Or dbSeeChanges
    ShipCntr = Qry.RecordsAffected
   
    'Create Jobrelease Records
    TransMsg = "Jobrelease Records"
    Set Qry = DB.QueryDefs![MultiJobsCreateReleaseQry]
    Qry.Execute dbFailOnError Or dbSeeChanges
    RelCntr = Qry.RecordsAffected

    If Not Me![Rework] Then
                 'Copy Operations
                 TransMsg = "Operations Records"
                 Set Qry = DB.QueryDefs![MultiJobsCreateOpsFromOpMstrQry]
                 Qry.Execute dbFailOnError Or dbSeeChanges
               
                 'Raw Matl Allocations - Just do all types - it is easier and maybe quicker than looking up which ones to do
                 TransMsg = "Sheet Style Raw Material Records"
                 Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlSheetQry]
                 Qry.Execute dbFailOnError Or dbSeeChanges
                 TransMsg = "Blank Style Raw Material  Records"
                 Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlBlankQry]
                 Qry.Execute dbFailOnError Or dbSeeChanges
                 TransMsg = "Generic Style Raw Material Records"
                 Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlGenericQry]
                 Qry.Execute dbFailOnError Or dbSeeChanges
                 TransMsg = "Tube Style Raw Material Records"
                 Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlTubeQry]
                 Qry.Execute dbFailOnError Or dbSeeChanges
        WS.CommitTrans: InTrans = False
       
                 '3/22/07 - Kim - SQL Fixes - I don't know WHY the kit query fails inside the transaction but it does
                 'BOM Allocations - 1 level only
                 TransMsg = "BOM Allocations Records for one level only."
                 Set Qry = DB.QueryDefs![MultiJobsCreateKitRecords2Qry]
                 Qry![AlDate] = Date
                 Qry![Skinitials] = Skinitials
                 Qry.Execute dbFailOnError Or dbSeeChanges
    Else
        WS.CommitTrans: InTrans = False
    End If

'This code DOES NOT work:



WS.BeginTrans: InTrans = True
    DoCmd.Hourglass True
    'Create Jobs Records
    TransMsg = "Jobs Records"
    Set Qry = DB.QueryDefs![MultiJobsCreateJobsQry]
    Qry![TopJob] = ""  '7/6/05 - Kim Added parameter really used by the multipart version
    Qry.Execute dbFailOnError Or dbSeeChanges
    JobCntr = Qry.RecordsAffected
   
    'Create Jobship Records
    TransMsg = "Jobship Records"
    Set Qry = DB.QueryDefs![MultiJobsCreateShipQry]
    Qry.Execute dbFailOnError Or dbSeeChanges
    ShipCntr = Qry.RecordsAffected
   
    'Create Jobrelease Records
    TransMsg = "Jobrelease Records"
    Set Qry = DB.QueryDefs![MultiJobsCreateReleaseQry]
    Qry.Execute dbFailOnError Or dbSeeChanges
    RelCntr = Qry.RecordsAffected

    If Not Me![Rework] Then
         'Copy Operations
         TransMsg = "Operations Records"
         Set Qry = DB.QueryDefs![MultiJobsCreateOpsFromOpMstrQry]
         Qry.Execute dbFailOnError Or dbSeeChanges
       
         'Raw Matl Allocations - Just do all types - it is easier and maybe quicker than looking up which ones to do
         TransMsg = "Sheet Style Raw Material Records"
         Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlSheetQry]
         Qry.Execute dbFailOnError Or dbSeeChanges
         TransMsg = "Blank Style Raw Material  Records"
         Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlBlankQry]
         Qry.Execute dbFailOnError Or dbSeeChanges
         TransMsg = "Generic Style Raw Material Records"
         Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlGenericQry]
         Qry.Execute dbFailOnError Or dbSeeChanges
         TransMsg = "Tube Style Raw Material Records"
         Set Qry = DB.QueryDefs![MultiJobsCreateRawMatlTubeQry]
         Qry.Execute dbFailOnError Or dbSeeChanges
         
         'BOM Allocations - 1 level only
         TransMsg = "BOM Allocations Records for one level only."
         Set Qry = DB.QueryDefs![MulitJobsCreateKitRecordsQry]
         Qry![aldate] = Date
         Qry![Skinitials] = Skinitials
         Qry.Execute dbFailOnError Or dbSeeChanges
    End If
WS.CommitTrans: InTrans = False


I am new to SQL Server so any tips to improve this code gladly accepted.
0
 
LVL 2

Author Comment

by:KMosher
ID: 18784494
Well, I solved the problem and I think we can thank good ol' Microsoft for this one.
I simply put the query
MulitJobsCreateKitRecordsQry
BEFORE
the query
MultiJobsCreateOpsFromOpMstrQry
and it works.
THESE TWO QUERIES ABSOLUTELY, POSITIVELY HAVE NO RELATION TO EACH OTHER WHATSOEVER!!! THERE ARE NO CONSTRAINTS HAVING ANYTHING TO DO WITH THESE TWO TABLES!
MulitJobsCreateKitRecordsQry
does have a relation to the records created in
MultiJobsCreateReleaseQry
but
MultiJobsCreateOpsFromOpMstrQry
has no relationships to anything created here.

While the queries are quite complex there is simply no reason for them not to work in the other order. Paricularly since there are four other queries ('Raw Matl Allocations) which come after the Ops query and continue to work just fine.

I want to thank everyone for their input. But if no one has any objections I will award the points to dqmq since he was the first one to tell me that as long as I was inside a transaction it should work. That is what got me looking in other directions to solve the problem.

0
 
LVL 42

Expert Comment

by:dqmq
ID: 18784806
That certainly looks like one transaction.  The only think I can think of is to check the Use Transaction property of  MulitJobsCreateKitRecordsQry query.  Also, what is the exact error?
0
 
LVL 11

Expert Comment

by:dready
ID: 18785348
Well, i'm happy it works, anyways!
0
 
LVL 2

Author Comment

by:KMosher
ID: 18789598
dqmq: That is the other thing that is interesting, it is unspecified. When that query tries to run it goes away for LONG time, almost like it has lost the connection. When it comes back all I get is "ODBC Error". No explanation whatsoever.

Anyway, thanks again for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
DCount using "OR" 4 19
SharePoint 2013 Relational Database 8 17
add more styles to my code 2 19
Access checkbox 2 0
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now