Insert Child before Parent
-
Hello,
How can I get the following to work? Transactions are still confusing to me.
Create Table Parent (Id Int Not Null Primary Key)
Create Table Child (Id Int Not Null, ParentId Int Not Null References Parent (Id))
This works:
Begin Tran
Insert Into Parent With (TABLOCKX) (Id) Values (3)
Insert Into Child With (TABLOCKX) (Id, ParentId) Values (1, 3)
Commit Tran
This doesn't work, but I would like it to:
Begin Tran
Insert Into Parent With (TABLOCKX) (Id) Values (3)
Insert Into Child With (TABLOCKX) (Id, ParentId) Values (1, 3)
Commit Tran
The main idea of a transaction is that it is atomic. Why should the order of inserts be relevant, if the end state doesn't violate any constraints? Why should insert hints even have to be considered in this situation?
Atomic? Perhaps this has something more to do with sub-atomic, quarks/neutrinos, and quantum phenomena, and the uncertainty principle.
Richard
-
Is it just me or you posted the same code twice??
Assuming you meant to swap the inserts, it will fail because the foreign key will cancel the transaction because no parent is found. It's like trying to have a child before the parent is born... it's just not the way things are supposed to work
. -
Hello,
Indeed I did post the same code twice, it should be:
Begin Tran
Insert Into Child With (TABLOCKX) (Id, ParentId) Values (1, 3)
Insert Into Parent With (TABLOCKX) (Id) Values (3)
Commit Tran
Many actions within a database do not have a corresponding action in the real world, but I can't come up with off the top of my head.
The A in Acid is Atomicity. If transactions are atomic, then why should part of this transaction fail? It would be interesting to know Celko's view on this.
Richard.
-
Under SQL Server, integrity constraints are checked when the action is performed not when the commit is performed.
Some other RDBMS do support an option that some integrity contstraints can be deferred until a commit, but SQL server does not support this functionallity.
SQL = Scarcely Qualifies as a Language
-
This works, but it's not elegant:
Alter Table Child NoCheck Constraint All
Insert Into Child With (Id, ParentId) Values (1, 3)
Insert Into Parent With (Id) Values (3)
Alter Table Child Check Constraint All
Oh well, that's the best I can do.
-
Works great... but you now have unvalidated data in the table so you might has well drop the constraint altogether.
-
There's no issue here, for three reasons:
1. The data went into Child unvalidated, but I can check it after re-enabling the constraint by issuing an update statement.
2. If you drop the constraint, you must assume that you know what the !@#$ you're doing.
3. The constraint (fk constraint that is) is re-enabled after the inserts, which will maintain the integrity between parent and child (in the future).
Richard
-
1 - Performance killer and also the db can do it for you so why bother??
2 - Yup you must know what you're doing... but even then dropping the constraint would be a stupid move in most cases (not assuming anything here).
3 - Re-enabled doesn't mean it will validate what you just did and what others might have done in the meantime. The bottom line is that you have unvalidated data and that unless this comes from a trusted source (like when you transfer data from server to server, where the data is already validated), then it's just pointless to have a FK in the first place.
-
RLSQL, I think that it would be best to decide whether you want to use the foreign key functionalities or not. Either drop the foreign key constraint altogether and leave just an index on the column, and then you can insert in any order you wish, and validate the data with your own means. Or leave the FK in place, but then make use of it and don't drop the constraint before insert.
A mix of both is IMHO the worst possible solution and I wouldn't recommend it.
-
i think you missed the point that each insert statement is an atomic transaction;
the begin tran is just a marker so that a group of atomic transactions can be rolled back....it is not a transaction itself, and thus would not be atomic in nature.
so the two insert statements are individually atomic...that's why the parent insert...child insert has no problems, but the reverse does.
from a business perspective, why do you feel you need to insert the child records first? what is the underlying reason why you think it needs to be done in that order?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Good morning,
Insert statements are only an atomic transaction if they run within an implied transaction, IE/no explicit transaction control. In the case where two inserts are executed within an explicit transaction, it would make sense to me, and arguably the ANSI-92 committee, that the constraints are checked after the commit occurs, not after the inserts occur. I wish someone like Celko could verify this for me.
I have developed a script of over 4,000 lines that creates new tables, inserts some test data, and also moves existing data into the new tables. The new tables are created with a multitude of constraints enabled, ie/Primary key, Foreign key, column checks, and even table level constraints. Those constraints are working very well, and it behooves the company I'm doing this for to do this; there are already multiple issues in the existing tables where children have no parents, tables exist without a primary key, and a 1-n relationships are not enforced using a pk/fk pair.
Which brings me to my problem. Everytime I run the script, any new data I programmatically added, after the last time I ran it, is eliminated. So lets say I run the script at 9am. Then I add some data to various new tables in the apps I'm developing. Then a change to the metadata is required, so I alter the script and re-run it. The result? The data inserted between 9am and when I "gen'd" is eliminated. Not very happy about that.
So what have I done? I've written a stored procedure "BackupRestore", which will save the content of all tables to TempDb before all the tables are dropped/re-created. The table rows are saved at the beginning of the script, and restored from the backup tables at the end of the script. It's working fairly well, I have to nail down a couple of issues. The complex part of the proc involves restoring the tables. Lets say you have over 30 tables that you want to restore. In what order are you going to restore them? One way is to analyze the dependencies/relationships, and restore the parent first, the child second, the grandchild third, and so on. This is an elegant way, but also time consuming and complex. You could iterate over sysobjects and sysreferences to create a "dependency map", which would then allow you to insert the parent table before the child. This becomes really tricky in my case because there are some tables that are self-joining. For example:
Create Table ApplicationSettingCategory (
Id Int Not Null Primary Key
, ApplicationId Int Not Null References Application (Id)
, ParentId Int Null References ApplicationSettingCategory (Id)
, Name T_Name Not Null
, Description T_Description Not Null
, Rank Int Not Null
, Constraint UniqueCategoryName Unique (ParentId, Name)
)
go
What this implies is that in addition to determining the order of inserts on a table by table basis, you must also determine the order of inserts in an individual table!'
In the end, the best approach to restore is, IMO:
{For each table to restore, in alphabetical order}
{Disable all constraints}
{Restore the table}
{Re-enable all constraints}
{End}
Check out my proc below, if you wish. Just remember it isn't complete.
Richard
---
Create Procedure BackupRestore
@InOp Char(1) = 'B' -- 'B' for backup, 'R' for restore.
As
Begin
Set NoCount On
Declare
@BackupMsg Varchar(100)
, @BackupTableName Varchar(100)
, @DynCmd NVarchar(2000)
, @IdCount Int
, @MaxCrDate DateTime
, @TableHasIdColumn Bit
, @TableName SysName
, @TableId Int
, @TableRowCount Int
Create Table #BackupTables (
Id Int Not Null
, Name SysName Not Null
, Restored Bit Not Null
)
Select @MaxCrDate = Max(CrDate) From SysObjects Where Type = 'U'
Insert Into #BackupTables (Id, Name, Restored)
Select Id, Name, '0' From SysObjects Where Type = 'U' And DateDiff(dd, CrDate, @MaxCrDate) = 0 Order By Name
Delete From #BackupTables Where (Name = 'Feedback') Or (Name = 'Incident') Or (Name = 'FormLetter') Or (Name = 'IFormLetter')
-- Select @BackupMsg = Cast(Count(0) as Varchar) + ' tables will be backed up and restored. These are: ' From #BackupTables
Select @BackupMsg = Cast(Count(0) as Varchar) + ' tables will be backed up and restored.'
If @InOp = 'B'
Begin
RaisError(@BackupMsg, 0, 1)
-- Select Name From #BackupTables Order By Name
End
If @InOp = 'B'
Begin
Select @TableId = Min(Id) From #BackupTables
While (@TableId Is Not Null)
Begin
Select @TableName = Name From #BackupTables Where Id = @TableId
Set @BackupTableName = 'Backup' + @TableName
Set @DynCmd =
'If Exists(Select * From TempDb..SysObjects Where Name = ' +
QuoteName(@BackupTableName, '''') + ') Drop Table TempDb..' + @BackupTableName
-- Print @DynCmd
Exec sp_ExecuteSql @DynCmd
Set @DynCmd = 'Select * Into TempDb..' + Cast(@BackupTableName as Char(40)) + ' From ' + @TableName
Exec sp_ExecuteSql @DynCmd
-- Print @DynCmd
Select @TableId = Min(Id) From #BackupTables Where Id > @TableId
End
End
Else
If @InOp = 'R'
Begin
Select @TableId = Min(Id) From #BackupTables
While (@TableId Is Not Null)
Begin
Select @TableName = Name From #BackupTables Where Id = @TableId
Set @DynCmd = 'Select @Count = Count(0) From ' + @TableName
Execute sp_ExecuteSql @DynCmd, N'@Count Int Output', @TableRowCount Output
-- If @TableRowCount = 0
Begin
Set @BackupTableName = 'TempDB..Backup' + @TableName
Set @DynCmd = 'Alter Table ' + @TableName + ' NoCheck Constraint All'
Exec sp_ExecuteSql @DynCmd
Set @TableHasIdColumn = 1
Set @DynCmd = 'Select @Count = Count(0) From SysColumns Where Name = ''Id'' And Id = Object_Id(' + QuoteName(@TableName, '''') + ')'
-- RaisError('Query for Id column: %s', 0, 1, @DynCmd)
Exec sp_ExecuteSql @DynCmd, N'@Count Int Output', @IdCount Output
If @IdCount = 0
Set @TableHasIdColumn = 0
If (@TableHasIdColumn = 1)
Begin
Set @DynCmd = 'Insert Into ' + @TableName + ' Select * From ' + @BackupTableName
Set @DynCmd =
'Insert Into ' + @TableName +
' Select * From ' + @BackupTableName + ' B ' +
' Where ' +
'Not Exists(Select Id From ' + @TableName + ' Where Id = B.Id)'
-- Print @DynCmd
Exec sp_ExecuteSql @DynCmd
If @@Error = 0
Update #BackupTables Set Restored = 1 Where Id = @TableId
End
Set @DynCmd = 'Alter Table ' + @TableName + ' Check Constraint All'
Exec sp_ExecuteSql @DynCmd
Set @DynCmd = 'Drop Table ' + @BackupTableName
Exec sp_ExecuteSql @DynCmd
End
Select @TableId = Min(Id) From #BackupTables Where Id > @TableId
End
-- Select @BackupMsg = Cast(Count(0) as Varchar) + ' tables were restored. These are: ' From #BackupTables Where Restored = '1'
Select @BackupMsg = Cast(Count(0) as Varchar) + ' tables were restored.'
RaisError(@BackupMsg, 0, 1)
-- Select Name From #BackupTables Where Restored = '1' Order By Name
End
Return(0)
End
go
-
Well certainly for a data load such as you describe, I suppose it is quite ok to disable constraints, etc on your tables because
- You are the only one putting data in them
- You trust yourself
- You trust the data that you are inserting (or are aware of its problems)
The large data movement would also run faster I imagine because SQL won't be checking constraints, etc (although with indices I am not sure how much of a speed difference it would make).
So yeah, in your situation, it may well be best. You could always write some simple validation select statements to select any child data in a child table that doesn't have an entry in its parent table - run them after your data has been restored.
-
And I guess it is precisely the reason why DTS can fail if you just tell it to "copy data" - it doesn't analyse table relationships, etc. You are effectively doing the same as what DTS does - so disable constraints or do the annoying work of figuring out what needs to be inserted in what order...
-
below is a procedure which returns all tables in the foreign key hiearachy order; it's copied and slightly adapted from scripts here at SSC;
i would suggest the following:
use this to get the tables and columns in hierarchy order. if you don't need the columns, search for "hierarchy" or "hierarchy2" here on SSC to get the original.
copy the data to temp the way you are doing it now
drop the tables.
recreate the tables.
move the data in the same order specified by the hierarchy results:
create procedure pr_GetTblColsInHierarcy
as
begin
set nocount on
declare
@level int,
@movedtonewlevel int,
@sql varchar(1024)
create table #hierarchy
(hierarchyorder int identity(1,1) not null primary key,fklevel int,
tblname varchar(517),
id numeric
)
-- populate the table
create table #tuser (type int, oname varchar(517), owner varchar(517), seq int)
insert #tuser exec sp_msdependencies null, 3, null, null, 1
insert into #hierarchy(tblname,fklevel)
select oname,seq from #tuser order by seq
--SELECT * FROM #hierarchy
insert into #hierarchy(tblname,fklevel)
select [name],0 from sysobjects where xtype='U' and [name] not in (select tblname collate latin1_general_cs_as from #hierarchy)
delete from #hierarchy where tblname like 'dt%'
select
sysobjects.name as tblname,
syscolumns.name as colname
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
left outer join #hierarchy on sysobjects.name=#hierarchy.tblname collate latin1_general_cs_as
where sysobjects.xtype='U' and sysobjects.name <> 'dtproperties'
order by hierarchyorder,sysobjects.name,syscolumns.name
drop table #hierarchy
end
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
this is probably better for you than the long crap i posted:
create table #tuser (type int, oname varchar(517), owner varchar(517), seq int)
insert #tuser exec sp_msdependencies null, 3, null, null, 1
select * from #tuser order by seq
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply