Problem on saving entity context with cascading sequence ORA
Posted: Thu 28 May 2009 10:00
Hi,
We have the exception ORA-02291 when we try to save a context with inserted data to push.
We have a table that have a foreign key on an another one, and both have a primary sequence id auto increment by a trigger.
The fact is that linq to entity doesnt seems to be able to get the sequence for populating the foreygn key value of the dependance value.
A sample code :
SQL code
Csharp code
Thanks.
We have the exception ORA-02291 when we try to save a context with inserted data to push.
We have a table that have a foreign key on an another one, and both have a primary sequence id auto increment by a trigger.
The fact is that linq to entity doesnt seems to be able to get the sequence for populating the foreygn key value of the dependance value.
A sample code :
SQL code
Code: Select all
-- SEQUENCES
CREATE SEQUENCE TESTPARENT_SEQ
START WITH 1
INCREMENT BY 1
NOCYCLE
NOORDER;
CREATE SEQUENCE TESTCHILD_SEQ
START WITH 1
INCREMENT BY 1
NOCYCLE
NOORDER;
--TABLES
CREATE TABLE TESTPARENT (
ID NUMBER,
VALUE VARCHAR2(20),
CONSTRAINT PK_TESTPARENT PRIMARY KEY (ID))
;
CREATE TABLE TESTCHILD (
ID NUMBER,
VALUE VARCHAR2(20),
FK_TESTPARENT NUMBER,
CONSTRAINT FK_TESTCHILD1 FOREIGN KEY (FK_TESTPARENT)
REFERENCES TESTPARENT(ID),
CONSTRAINT PK_TESTCHILD PRIMARY KEY (ID))
;
-- TRIGGERS
CREATE OR REPLACE TRIGGER TESTPARENT_BEF_INS
BEFORE INSERT OR UPDATE ON TESTPARENT FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT TESTPARENT_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END IF;
END;
/
CREATE OR REPLACE TRIGGER TESTCHILD_BEF_INS
BEFORE INSERT OR UPDATE ON TESTCHILD FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT TESTCHILD_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
--SELECT TESTPARENT_SEQ.CURRVAL INTO :NEW.FK_TESTPARENT FROM DUAL;
END IF;
END;
/
Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Test.Console
{
class Program
{
static void Main(string[] args)
{
Entities entities = new Entities();
TESTPARENT testParent = new TESTPARENT();
testParent.VALUE = "PARENT";
TESTCHILD testChild = new TESTCHILD();
testChild.VALUE = "CHILD";
testParent.TESTCHILD.Add(testChild);
entities.AddToTESTPARENT(testParent);
try
{
entities.SaveChanges();
}
catch (Exception exc)
{
System.Diagnostics.Debug.WriteLine(exc);
throw;
/// Devart.Data.Oracle.OracleException: ORA-02291: violation de contrainte d'intégrité (SEGULA.FK_TESTCHILD1) - clé parent introuvable
/// => integrity constraint violated - parent key not found
}
System.Console.ReadKey();
}
}
}
Thanks.