Problem on saving entity context with cascading sequence ORA

Problem on saving entity context with cascading sequence ORA

Postby mackjb » 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
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;
/


Csharp code
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.
mackjb
 
Posts: 2
Joined: Thu 28 May 2009 09:25
Location: France

Postby AndreyR » Thu 28 May 2009 10:36

Have you set the StoreGeneratedPattern attribute for the auto-generated columns to "Identity" in your storage part of the model?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby mackjb » Thu 28 May 2009 11:48

Thanks that's exactly this.
mackjb
 
Posts: 2
Joined: Thu 28 May 2009 09:25
Location: France


Return to dotConnect for Oracle