php - PDO:SQLite INSERT 未保存

Question

我使用以下代码创建了一些表:

$db = new PDO(SQLITE_PATH);
$statement = $db->prepare('CREATE TABLE States (name VARCHAR(25) NOT NULL PRIMARY KEY);');
$statement->execute();
$statement = $db->prepare('CREATE TABLE Events (DBID INT NOT NULL PRIMARY KEY , CalID INT, start INT, end INT, state VARCHAR(25), FOREIGN KEY(state) REFERENCES States(name));');
$statement->execute();

并在表“States”中创建了一个条目“Wochenende”。

当我想使用以下代码将条目插入“事件”表时:

$from_timestamp = strtotime(date('c'));
$to_timestamp = strtotime(date('c', time()+100000));

$query = 'INSERT INTO Events (CalID,start,end,state) VALUES (:CALID, :START, :END, :STATE);';
$statement = $db->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
if (!$statement) {
        print_r($db->errorInfo());
}
$statement->execute(array(':CALID' => get_highest_calid($db), ':START' => $from_timestamp, ':END' => $to_timestamp, ':STATE' => 'Wochenende'));

没有返回错误，数据库中仍然没有条目。

Answer 1

检查 errorInfo() 的结果调用 execute() 后的方法显示此错误消息:

NOT NULL constraint failed: Events.DBID

它被声明为DBID INT NOT NULL PRIMARY KEY那么为什么它不自动添加下一个序列号呢？

根据SQLite documentation :

A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER".

因此您需要将列定义更改为DBID INTEGER PRIMARY KEY。

<小时/>

奖金信息:

启用PDO exceptions这样这些问题就变成了 fatal error ，并且不需要手动调用errorInfo():

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

这会引发以下异常并停止执行:

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: Events.DBID in ...

<小时/>

奖金信息 2:

由于您要声明外键，因此您需要在运行时启用对它们的支持，如 foreign key constraints are disabled by default (for backwards compatibility) :

$db->exec('pragma foreign_keys = ON');