Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2003-10-13 : 17:39:06
|
| Hi,If you add a record, a, which references record, b, in another table and there's a foreign key constraint in effect at what point is the constraint in operation?If I have a transaction and record a, complete with reference, is added in the middle of it, will a concurrent process be able to delete record b after the insert but before the end of the transaction doing the insert or is the constraint in operation as soon as the insert is completed within the transaction even if that transaction is ultimately rolled back?Cheers,X-Factor |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-13 : 17:49:00
|
| The constraint is always in effect.Integrity will be maintained no matter which order the actions are carried out in.When a record is inserted into the child table it will need to access the parent (the unique index on the parent to be exact) to check the integrity. When a record is inserted into the parent table the index will be locked until the completion of the insert and inserts into the child table will be blocked.For a delete from the parent it will need to access the child table to check for integrity (a table scan if there is no index so may take some time). If a record is being inserted/deleted from the child table this will be blocked until completion.Inserts/deletes are not complete until the transaction is committed so the other spid will be blocked until this time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
When is a foreign key constraint in effect?