SQL & PL/SQL

Hello,

I encountered error ORA-02291 when I tried to use MERGE statement on the table with "self-referenced" foreign key. Using the foreign key deferrable did not help. The only one thing, which helped me, was using errorlog table. See the demonstration:


Working as common user:

SQL> CONNECT scott/tiger

-----

First of all, I create table and (not deferrable) constraints:

CREATE TABLE fkv (
id NUMBER(1) CONSTRAINT nn_fkv_id NOT NULL,
parent_id NUMBER(1) CONSTRAINT nn_fkv_paid NOT NULL
);
ALTER TABLE fkv ADD CONSTRAINT pk_fkv_id PRIMARY KEY (id);
ALTER TABLE fkv ADD CONSTRAINT fk_fkv_paid FOREIGN KEY (parent_id) REFERENCES fkv(ID) NOT DEFERRABLE;

-----

INSERT is working well:

INSERT INTO fkv (
id,
parent_id
)
SELECT
1,
1
FROM
DUAL;

COMMIT;

1 rows inserted.
commited.

-----

MERGE statement using UPDATE branch is working well too:

MERGE INTO fkv USING (
SELECT
1 AS ID,
1 AS PARENT_ID
FROM
DUAL
) a
ON (
fkv.id = a.id
)
WHEN MATCHED THEN
UPDATE SET
fkv.parent_id = a.parent_id
WHERE
A.ID IS NOT NULL
DELETE WHERE a.id IS NULL
WHEN NOT MATCHED THEN
INSERT (
ID,
parent_id
)
VALUES (
A.ID,
A.PARENT_ID);

COMMIT;

1 rows merged.
commited.

-----

And now is coming the strange behaviour:

MERGE INTO fkv USING (
SELECT
2 AS id,
2 AS PARENT_ID
FROM
DUAL
) a
ON (
fkv.id = a.id
)
WHEN MATCHED THEN
UPDATE SET
fkv.parent_id = a.parent_id
WHERE
A.ID IS NOT NULL
DELETE WHERE a.id IS NULL
WHEN NOT MATCHED THEN
INSERT (
ID,
parent_id
)
VALUES (
A.ID,
A.PARENT_ID);

SQL Error: ORA-02291: integrity constraint (SCOTT.FK_FKV_PAID) violated - parent key not found

ROLLBACK;

rollback complete.

-----

Ok, even it is not a good solution, I try deferrable constraint:

ALTER TABLE fkv DROP CONSTRAINT fk_fkv_paid;
ALTER TABLE fkv ADD CONSTRAINT fk_fkv_paid FOREIGN KEY (parent_id) REFERENCES fkv(id) DEFERRABLE INITIALLY DEFERRED;

table FKV altered.
table FKV altered.

MERGE INTO fkv USING (
SELECT
2 AS id,
2 AS PARENT_ID
FROM
DUAL
) a
ON (
fkv.id = a.id
)
WHEN MATCHED THEN
UPDATE SET
fkv.parent_id = a.parent_id
WHERE
A.ID IS NOT NULL
DELETE WHERE a.id IS NULL
WHEN NOT MATCHED THEN
INSERT (
ID,
parent_id
)
VALUES (
A.ID,
A.PARENT_ID);

1 rows merged.

COMMIT;

SQL Error: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_FKV_PAID) violated - parent key not found

... deffered constraint did not help :-(

-----

Let's try another way - errorlog table; for the first with the not deferrable constraint again:

ALTER TABLE fkv DROP CONSTRAINT fk_fkv_paid;
ALTER TABLE fkv ADD CONSTRAINT fk_fkv_paid FOREIGN KEY (parent_id) REFERENCES fkv(ID) NOT DEFERRABLE;

table FKV altered.
table FKV altered.

BEGIN
sys.dbms_errlog.create_error_log (
dml_table_name => 'FKV',
err_log_table_name => 'ERR$_FKV'
);
END;

anonymous block completed

-----

Toys are prepared, let's start with error logging:

MERGE INTO fkv USING (
SELECT
2 AS id,
2 AS PARENT_ID
FROM
DUAL
) a
ON (
fkv.id = a.id
)
WHEN MATCHED THEN
UPDATE SET
fkv.parent_id = a.parent_id
WHERE
A.ID IS NOT NULL
DELETE WHERE a.id IS NULL
WHEN NOT MATCHED THEN
INSERT (
ID,
parent_id
)
VALUES (
A.ID,
A.PARENT_ID)
LOG ERRORS INTO err$_fkv;

1 rows merged.

-----

Cannot belive, running SELECT for confirmation:

SELECT * FROM err$_fkv;
SELECT * FROM fkv;

no rows selected

ID PARENT_ID
-- --
1 1
2 2

-----

Ok, COMMIT:

COMMIT;

commited.

-----

SELECT for confirmation again:

SELECT * FROM err$_fkv;
SELECT * FROM fkv;

no rows selected

ID PARENT_ID
-- --
1 1
2 2



Using deffered constraint and error logging is working well too.

Metalink and Google did not help me. I am using databases 10.2.0.5 and 11.2.0.3.

Has somebody encountered this problem too or have I missed something?

Thank you

D.