foreign keys and sqlite

Yii 1.1 General Discussion
1

Using: yii 1.1.8, php 5.3.3, sqlite 3.7.9 on CentOS 5.7

I am using foreign keys with ON DELETE CASCADE to maintain my table integrity. Everything works OK for MySQL. Also, if I use the sqlite3 command line interface everything seems to work right. It is just when I use sqlite and do things in code that it doesn’t seem to work.

Here are the basic structures of 3 tables that I have: tbl_user, tbl_group, tbl_user_group

[sql]CREATE TABLE tbl_user (

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,


username VARCHAR(128) NOT NULL UNIQUE,

);

CREATE TABLE tbl_group (

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,


groupname VARCHAR(128) NOT NULL UNIQUE,

);

CREATE TABLE tbl_user_group (

user_id INTEGER NOT NULL,


group_id INTEGER NOT NULL,


FOREIGN KEY(user_id) REFERENCES tbl_user(id) ON DELETE CASCADE,


FOREIGN KEY(group_id) REFERENCES tbl_group(id) ON DELETE CASCADE

);

[/sql]

Within config/main.php I have the database connection defined:


		'db'=>array(

			// SQLITE3 Settings

			'connectionString' => 'sqlite:'.dirname(__FILE__).'/../data/dev.db',

			'initSQLs'=>array(

				'PRAGMA foreign_keys = ON',

			),

		),

I am using the activerecord class (CActiveRecord) for managing the tables. If I have a user record defined and a group record defined and I associate them with each other via the tbl_user_group table, when I delete a user then the associated record in the tbl_user_group should be automatically deleted because of the ON DELETE CASCADE.

This works for MySQL (as long as it is using the Innodb engine). If I use the sqlite3 command line interface and turn on foreign key support with:

PRAGMA foreign_keys = ON;

and delete the user record from within the CLI then it also deletes the associated tbl_user_group record in sqlite. It just doesn’t do it with sqlite when I execute this code in UserController.php inside the actionDelete function:


			$model = $this->loadModel($id);

			$model->delete();

It seems like the ‘initSQLs’ is not working correctly to turn foreign key support on within sqlite. Has anyone gotten this type of thing to work?

2

Found out the answer on this one so I thought I would report back.

Turns out that just because I installed a newer version of sqlite (3.7.9) on my system, that new version is not automatically what is used by PHP. PHP will use whatever PDO version is compiled into it. I discovered this by running phpinfo and saw that the version of sqlite that was being used within PHP was an older version than what I had installed and it was one that did not have foreign key support.

I chose to download and compile a new version of PHP (5.3.8). Using the instructions at linuxadminzone.com/download-compile-install-and-configure-php-5-3-5-in-linux as a guideline and with a lot of persistence, I finally got it all working. It was not a simple task and if I had to do it all over again I would probably just start with a new LAMP install. It would probably have taken less time.