Update to Child before Parent Insert
Posted: Thu 12 May 2011 17:34
I've found what i believe is a bug in the order the dataContext submits SQL to the server. If you have a child record with a nullable FK to the Parent in the database already, then attempt to insert the parent and associate to the child in one transaction, the update to the child hits prior to the parent being inserted. This ultimately results in a FK violation because PK 0 is not found in the parent table. See code and schema below for my test.
Here's my console app code:
Here's SQL to generate simple test schema used:
Thanks,
Zach
Here's my console app code:
Code: Select all
Module Module1
Sub Main()
Try
Dim databaseMonitor As New Devart.Data.Oracle.OracleMonitor()
databaseMonitor.IsActive = True
' This first section happens some time before the user will return (web application)
Dim insertLastWeek As New TestDataContext.TestDataContext()
Dim aChild As New TestDataContext.Childtable()
aChild.Mydata = "Hello"
insertLastWeek.Childtables.InsertOnSubmit(aChild)
insertLastWeek.SubmitChanges()
' ========================================================================
' ========================================================================
' the user has returned to website and we need
' to create the parentTable record
Dim dc As New TestDataContext.TestDataContext()
Dim c = dc.Childtables.FirstOrDefault(Function(w) w.Parenttableid Is Nothing)
Dim p As New TestDataContext.Parenttable()
p.Mydata = "Good Bye"
'' Test One - fail
'c.Parenttable = p
'' Test Two - fail
'p.Childtables.Add(c)
'' Test Three - fail
'dc.Parenttables.InsertOnSubmit(p)
'c.Parenttable = p
' Test Four - Success... but in two database transactions.
dc.Parenttables.InsertOnSubmit(p)
dc.SubmitChanges()
p.Childtables.Add(c)
' Always submit
dc.SubmitChanges()
Catch ex As Exception
Console.WriteLine("Error Occured!!")
showError(ex, 1)
End Try
Console.WriteLine()
Console.WriteLine("-- End of program. Press any key to close --")
Console.ReadKey(True)
End Sub
Private Sub showError(ByVal ex As Exception, ByVal count As Integer)
Console.WriteLine()
Console.WriteLine(count.ToString() & ": " & ex.Message)
If ex.InnerException IsNot Nothing Then showError(ex.InnerException, count + 1)
End Sub
End Module
Code: Select all
/*
-- Drop talbes and values
DROP TABLE PARENTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDTABLE CASCADE CONSTRAINTS PURGE;
-- Drop sequences
DROP SEQUENCE PARENTTABLE_SEQ;
DROP SEQUENCE CHILDTABLE_SEQ;
*/
-- Create the Tables
CREATE TABLE parenttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL
);
CREATE TABLE childtable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER,
CONSTRAINT parenttable_childtable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);
-- Create the Sequences
CREATE SEQUENCE PARENTTABLE_SEQ NOCACHE;
CREATE SEQUENCE CHILDTABLE_SEQ NOCACHE;
-- Create the Triggers
CREATE TRIGGER PARENTTABLE_BEFORE_INSERT
BEFORE INSERT ON PARENTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT PARENTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/
CREATE TRIGGER CHILDTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/
Zach