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