douping5015
douping5015
2012-11-23 18:12

PDO上的sqlite3外键

已采纳

I use PDO for connecting to sqlite3, but I cannot make foreign keys working for some reason. According to docs, this "PRAGMA short_column_names=1" should enable that. I do this:

$con = new PDO('sqlite:z:/testing.db');
$res = $con->exec('PRAGMA foreign_keys=ON');
var_dump($res);die();

Which returns me 0. I tried creating actual tables with foreign keys and it did not work. Direct requests to SQLite3 class worked though:

$con = new SQLite3('z:/testing.db');
$con->exec('PRAGMA foreign_keys = ON;');
var_dump($con->query('PRAGMA foreign_keys;')->fetchArray());

This reutrns array(2) { [0]=> int(1) ["foreign_keys"]=> int(1) }

According to SQLite3::version(), I have sqlite version 3.7.7.1. My PHP version is 5.3.18, running on Windows.

Please help me get it running with PDO. Thanks!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douyin7829 douyin7829 9年前

    I'm not aware of the idea that PRAGMA short_column_names=1; should be issued with PDO in order for foreign key constraints to work with SQLite. You should however issue PRAGMA foreign_keys = ON; with PDO as well.

    Give this a try:

    <?php
    
    // SQL for creating database structure
    $databaseSql = <<<SQL
        CREATE TABLE `user` (
            `id` INTEGER PRIMARY KEY AUTOINCREMENT,
            `name` TEXT NOT NULL,
            UNIQUE( `name` )
        );
    
        CREATE TABLE `userProfile` (
            `userId` INTEGER NOT NULL CONSTRAINT `userProfile_userId` REFERENCES `user`( `id` ) ON UPDATE CASCADE ON DELETE CASCADE,
            `image` TEXT NOT NULL
        );
    SQL;
    
    // SQL for inserting dummy data
    $dataSql = <<<SQL
        INSERT INTO `user` VALUES( 1, "John" );
        INSERT INTO `user` VALUES( 2, "Mary" );
        INSERT INTO `user` VALUES( 3, "Joe" );
        INSERT INTO `userProfile` VALUES( 1, "/images/john.jpg" );
        INSERT INTO `userProfile` VALUES( 2, "/images/mary.jpg" );
        INSERT INTO `userProfile` VALUES( 3, "/images/joe.jpg" );
    SQL;
    
    // create a temporary SQLite instance in memory
    $db = new PDO( 'sqlite::memory:', null, null, array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ) );
    
    // activate use of foreign key constraints
    $db->exec( 'PRAGMA foreign_keys = ON;' );
    
    // create database
    $db->exec( $databaseSql );
    // insert dummy data
    $db->exec( $dataSql );
    
    // should dump 3 records
    var_dump( $db->query( 'SELECT * FROM `userProfile`;' )->fetchAll() );
    
    // delete 1 user, cascade deleting 1 userProfile as well
    $db->exec( 'DELETE FROM `user` WHERE `id` = 1;' );
    
    // should dump 2 records
    var_dump( $db->query( 'SELECT * FROM `userProfile`;' )->fetchAll() );
    

    It should result in the following:

    array(3) {
      [0]=>
      array(2) {
        ["userId"]=>
        string(1) "1"
        ["image"]=>
        string(16) "/images/john.jpg"
      }
      [1]=>
      array(2) {
        ["userId"]=>
        string(1) "2"
        ["image"]=>
        string(16) "/images/mary.jpg"
      }
      [2]=>
      array(2) {
        ["userId"]=>
        string(1) "3"
        ["image"]=>
        string(15) "/images/joe.jpg"
      }
    }
    array(2) {
      [0]=>
      array(2) {
        ["userId"]=>
        string(1) "2"
        ["image"]=>
        string(16) "/images/mary.jpg"
      }
      [1]=>
      array(2) {
        ["userId"]=>
        string(1) "3"
        ["image"]=>
        string(15) "/images/joe.jpg"
      }
    }
    
    点赞 评论 复制链接分享

相关推荐