lundi 24 août 2015

Constraint violation when using explicit data_type in PDO for SQLite foreign key

I am using PHP PDO with SQLite database. This is the schema of the table I am working on:

CREATE TABLE "quests" (
    `id`    TEXT,
    `parent`    TEXT,
    PRIMARY KEY(id),
    FOREIGN KEY(`parent`) REFERENCES quests(id)
)

... and contains one line which has hello as id.

And next is the PHP script I am using (less unrelated debug lines):

<?php

$dbhandle = new PDO("sqlite:sutori.sqlite3");
$dbhandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbhandle->exec("PRAGMA foreign_keys = ON;");

$sth = $dbhandle->prepare("INSERT INTO quests VALUES ( :id, :parent );");

$sth->bindValue(":id", "nouvelles", SQLITE3_TEXT);
$sth->bindValue(":parent", "hello", SQLITE3_TEXT);

$sth->execute();
die();

?>

The above script will return this SQL error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed' in /var/www/html/sutori/sutori.php:56

But if I remove the data_type argument from bindValue of the foreign key field as below:

$sth->bindValue(":id", "nouvelles", SQLITE3_TEXT);
$sth->bindValue(":parent", "hello"); // SQLITE3_TEXT removed

... it works, so the INSERT is done.

I know the script would work as expected if I don't specify the data_type argument. But do you have any idea why I am getting this behavior? Is it a PDO bug/feature or is my script wrong?

Thank you for your inputs! :)

Aucun commentaire:

Enregistrer un commentaire