php oci: uncommitted foreign key needed for insert

Question

So my situation is:

I have 2 tables:

• TABLE_A
• TABLE_B

---

• TABLE_A has an ID as Primary key as an identity
• TABLE_B has an ID as identity and a_ID as Foreign key Referencing the ID from TABLE_A

---

I use oci_execute with OCI_NO_AUTO_COMMIT so if an insert fails later on, the inserts simply wont happen. And if everyting runs successfully I make a commit at the end;

The Problem

I run an insert on TABLE_A that returns the inserted rows ID that I want to use righ after. But since it hasn't been committed yet I get a foreign key violation error.

Possible solution

• I thought of doing a prewritten PL/SQL script but I may run multiple inserts so I would end up string replacing and concatenating multiple inserts strins instead of properly binding variables.
• I even thought of inserting the row anyways and deleting if something fails but that sounds so bad, I'd rather do the string query manipulation.
• Maybe TABLE_A should have a 0 ID that nothing uses and I first use that. Than later on I change it to the inserted one but this sounds horrible as well.

Is there any other way I could do this?

Answer 1

I don't know PHP, but - see if my Oracle side of view helps.

This is what you have now:

SQL> create table table_a (id    number primary key);

Table created.

SQL> create table table_b (id    number primary key,
  2                        id_fk number references table_a
  3                       );

Table created.

SQL> insert into table_b values (1, 1);
insert into table_b values (1, 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C00105535) violated - parent key not
found

Of course it won't work ... there's no parent key in table_a.

---

But, if you change foreign key constraint so that it "ignores" whether referenced key exists or not and make it check it at COMMIT point, this might be what you're looking for.

See lines #3 and #4:

SQL> drop table table_b;

Table dropped.

SQL> create table table_b (id    number primary key,
  2                        id_fk number constraint fk_ba references table_a
  3                                       initially deferred deferrable
  4                       );

Table created.

SQL> insert into table_b values (1, 1);

1 row created.

See? Insert succeeded, although referenced primary key in table_a doesn't exist yet.

So, once you enter it as well ...

SQL> insert into table_a values (1);

1 row created.

... and commit ...

SQL> commit;

Commit complete.

SQL>

... everything is here:

SQL> select * From table_a;

        ID
----------
         1

SQL> select * from table_b;

        ID      ID_FK
---------- ----------
         1          1

SQL>

---

If the problem is related to PHP, then I'm afraid I can't help.