我正在创建一个NOT NULL列的表，如下所示:
CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Username TEXT UNIQUE NOT NULL, Password TEXT NOT NULL);
但是SQLite很乐意为这些列添加NULL值:
<?php
error_reporting(E_ALL);
date_default_timezone_set('Europe/Helsinki');
ini_set('default_charset', 'UTF-8');
mb_internal_encoding("UTF-8");
header('Content-Type: text/html; charset=UTF-8');
// ---
const DB_FILE = "auth.db";
//$username = "john";
//$password = "secret";
$username = '';
$password = '';
// ---
// open db
//$db = new PDO('sqlite:'.DB_FILE);
$db = new PDO('sqlite::memory:');
// set attributes
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
// create the table
$db->exec("CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Username TEXT UNIQUE NOT NULL, Password TEXT NOT NULL);");
$statement = "INSERT INTO Users (Username, Password) VALUES (:Username,:Password);";
$query = $db->prepare($statement);
$query->bindValue(':Username', $username, SQLITE3_TEXT);
$query->bindValue(':Password', $password, SQLITE3_TEXT);
$ok = $query->execute();
$result = $db->query('SELECT * FROM Users');
if (!$result) die ("oops!"); // will not produce anything when errormode is on
echo "<pre>'n";
foreach($result as $row)
{
echo "Id: " . $row['Id'] . "'n";
echo "Username: " . $row['Username'] . "'n";
echo "Password: " . $row['Password'] . "'n";
}
$db = NULL;
?>
SQLite不允许这样做。我做错了什么?
更新时间:2013-04-23 ~00:35 Z
以下是对Diamond Geezer的回答的关注评论。
SQLite停止执行以下语句:
$db->exec("INSERT INTO Users (Username, Password) VALUES ('','');");
$db->exec("INSERT INTO Users (Username, Password) VALUES ('a','aa');");
$db->exec("SELECT * FROM Users;");
但不是这些:
$username = '';
$password = '';
$statement = "INSERT INTO Users (Username, Password) VALUES (:Username,:Password);";
$query = $db->prepare($statement);
$query->bindValue(':Username', $username, SQLITE3_TEXT);
$query->bindValue(':Password', $password, SQLITE3_TEXT);
$ok = $query->execute();
我想这一定和准备语句有关。
用户名和密码不为空;它们是零长度字符串:
$username = '';
$password = '';
这就是它们被插入的原因。
作为对您的评论的回应，您可以使用CHECK约束来停止插入零长度字符串，如下所示。
CREATE TABLE IF NOT EXISTS Users
(
Id INTEGER PRIMARY KEY,
Username TEXT UNIQUE NOT NULL CHECK(Username<>''),
Password TEXT NOT NULL CHECK(Password<>'')
);
这是在SQL Fiddle。语句:INSERT INTO Users (Username, Password) VALUES ('','');失败，错误:could not execute statement due to a constraint failure (19 constraint failed)，而语句:INSERT INTO Users (Username, Password) VALUES ('a','aa');成功。