How to defer constraints in PostgreSQL with PHP PDO?

Question

I have two tables:

CREATE TABLE main_table
(
  id integer NOT NULL,
  field character(10) NOT NULL,
  CONSTRAINT main_table_pkey PRIMARY KEY (field)
)
WITH (
  OIDS=FALSE
);

And

CREATE TABLE child_table
(
  id integer NOT NULL,
  child_field character(10) NOT NULL,
  CONSTRAINT child_table_pkey PRIMARY KEY (id),
  CONSTRAINT main_table_field_fkey FOREIGN KEY (child_field)
      REFERENCES main_table (field) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

As you can see child_table has foreign key to main_table by child_table.child_field -> main_table.field

I want to have possibility to insert first in child_table then in main_table. For example:

INSERT INTO child_table(id, child_field) VALUES (1, '0123456789');
INSERT INTO main_table(id, field) VALUES(1, '0123456789');

First I thought that transactions help me. But even if I execute 'START TRANSACTION' first INSERT query throws constraint error and transaction fails before commit.

After I tried to add DEFERRABLE instruction to 'START TRANSACTION'. The same result. Then I add to my foreigh key to 'DEFERRABLE INITIALLY DEFERRED' to my foreigh key, so I've got:

CREATE TABLE child_table
(
  id integer NOT NULL,
  child_field character(10) NOT NULL,
  CONSTRAINT child_table_pkey PRIMARY KEY (id),
  CONSTRAINT main_table_field_fkey FOREIGN KEY (child_field)
      REFERENCES main_table (field) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
  OIDS=FALSE
);
ALTER TABLE child_table
  OWNER TO postgres;

After this I've got posibillity to execute my INSERTs by command line even without transaction.

But anyway I could not to do the same with PDO PHP. I tried prepared queries, it didn't work. Even direct queries doesn't work:

$pdo->exec("INSERT INTO child_table(id, child_field) VALUES (1, '0123456789')");
$pdo->exec("INSERT INTO main_table(id, field) VALUES(1, '0123456789')");

Starting transaction don't help too. Query 'SET CONSTRAINTS ALL DEFERRED' doesn't help too. I tried to add 'PDO::ATTR_PERSISTENT => true' to init array. No results. So please tell me, how I can execute these queries in this order by PHP? As I think, I've got to execute these queries by one transaction, but this transaction have not to check constraints until it will be committed. Or I am I wrong?

SOLUTION

The problem was not with PDO and not with transactions. Table main_table was created by another role and seems after commit data did not write into main_table because of owner problem, so child_table throws constraint error. It's bad that main_table did not throws owner error, so problem was not obvious.

Somebody answered me with this solution:

$pdo->beginTransaction();
$pdo->exec("INSERT INTO child_table(id, child_field) VALUES (1, '0123456789')");
$pdo->exec("INSERT INTO main_table(id, field) VALUES(1, '0123456789')");
$pdo->commit();

Man, this was right solution, I'm sorry that I answer you that it doesn't work.

The real question is why can't you just reverse the order of the inserts? Or change the constraint? http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit — Digital Chris, Nov 08 '15 at 20:56
My example is just simplifier example of my problem. First INSERT in fact are many INSERTs in for loop, AND my constraints tree more difficult. First mind it is all constarints already created. In my code I have not possibility to reorder these INSERTs. The main problem (real question) it's why my INSERTs work in command line, but doesn't work with PDO. In the articles of your link there's MySQL problem described. I don't know, maybe MySQL cannot to do this. But Postgres can, because I execute these queries by command line. — abr_stackoverflow, Nov 08 '15 at 21:04

How to defer constraints in PostgreSQL with PHP PDO?

0 Answers0