php - sqlite中的PDO::beginTransaction()和外键冲突

Question

我将使用SQLite help page中有关外键约束的示例：

让我们在SQLite中构建2个表：

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER PRIMARY KEY, 
  trackname   TEXT, 
  trackartist INTEGER,
  CONSTRAINT fk
  FOREIGN KEY(trackartist) REFERENCES artist(artistid))
  ON UPDATE CASCADE ON DELETE CASCADE
);

让我们从SQLite命令添加2条记录：

sqlite> pragma foreign_keys = ON;
sqlite> INSERT INTO artist(artistid, artistname) VALUES(null, 'Bing Crosby');
sqlite> INSERT INTO track(trackid, trackname, trackartist) VALUES(null, 'White Christmas', 1);
sqlite> SELECT * FROM artist;
1|Bing Crosby
sqlite> SELECT * FROM track;
1|White Christmas|1

现在，尽管命令工具不允许（正确）插入：

sqlite> INSERT INTO track(trackid, trackname, trackartist) VALUES(null, 'Another Track', 9);
Error: FOREIGN KEY constraint failed

另一方面，php允许（错误地）这样做：

$dbh = new \PDO('sqlite:test.sq3');
$dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
try {
   if (!$dbh->beginTransaction())
      throw new \Exception('Could not started a transaction!');
   //$dbh->exec('PRAGMA foreign_keys = ON;');
   $query = $dbh->prepare('PRAGMA foreign_keys = ON;');
   $query->execute();
   //$dbh->exec("INSERT INTO track(trackid, trackname, trackartist) VALUES(null, 'Another Track', 9);");
   $query = $dbh->prepare("INSERT INTO track(trackid, trackname, trackartist) VALUES(null, 'White Christmas', 9);");
   $query->execute();
   $dbh->commit();
} catch (\Exception $e) {
   $dbh->rollback();
}

看到违规：

sqlite> SELECT * FROM track;
1|White Christmas|1
2|Another Track|9

任何想法？谢谢！

Answer 1

documentation说：


这种务实是交易中的禁忌。仅当没有待处理的BEGIN或SAVEPOINT时，才可以启用或禁用外键约束强制。