SQLite Foreign Key Enforcement

[NickPerkins]

Apparently, new versions of SQLite will enforce Foreign Keys ( unlike
previous versions ), but, for the sake of backwards-compatibility, you
have to explicitly turn on foreign key enforcement by issuing: "pragma
foreign_keys=on;".

http://www.sqlite.org/foreignkeys.html

I am new to SQLAlchemy -- how can I implement this in my program?
The docs say that it must be turned on for each database connection
separately.

[Michael Bayer]

Apply a PoolListener to your engine which implements this call for each new connection:

http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener

>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[NickPerkins]

Thanks, I tried it...but could not get the desired result ( enforced
FKs using SQLite ).
Here is my test code:

#!/usr/bin/env python
import sqlalchemy
from sqlalchemy.interfaces import PoolListener

class MyListener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.execute('PRAGMA foreign_keys = ON;')

engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True,
listeners=[MyListener()])

metadata = sqlalchemy.MetaData(bind=engine)

from sqlalchemy import Table, Column, ForeignKey, Integer

parent_table = Table('parent',metadata,
Column('id',Integer,primary_key=True)
)

child_table = Table('child',metadata,
Column('id',Integer,primary_key=True),

Column('parent_id',Integer,ForeignKey('parent.id'))
)

metadata.create_all(bind=engine)

engine.connect()
engine.execute(parent_table.insert().values(id=44))

try:
engine.execute(child_table.insert().values(id=55,parent_id=33))
print 'Foreign Keys were NOT enforced!'
except:
print 'Foreign Keys were enforced.'

------------------------------------
The result I get is :"not enforced".
using SQLite 3.6.23 on Windows
Am I doing it right?

On Apr 12, 8:26 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Apr 12, 2010, at 7:22 PM, NickPerkins wrote:
>
> > Apparently, new versions of SQLite will enforce Foreign Keys ( unlike
> > previous versions ), but, for the sake of backwards-compatibility, you
> > have to explicitly turn on foreign key enforcement by issuing: "pragma
> > foreign_keys=on;".
>
> >http://www.sqlite.org/foreignkeys.html
>
> > I am new to SQLAlchemy -- how can I implement this in my program?
> > The docs say that it must be turned on for each database connection
> > separately.
>
> Apply a PoolListener to your engine which implements this call for each new connection:
>

> http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?h...

[Michael Bayer]

have you confirmed this works with a raw sqlite3 connection ?

[NickPerkins]

I have confirmed that it works from the sqlite3 interactive session:

sqlite> insert into child values(33);
Error: foreign key mismatch

I will try it with pysqlite...see what happens...

[NickPerkins]

It seems that I have more than one version of SQLite installed!
When I run from Python, it picks up an older version than the new one
I put in the project directory.
I just have to figure out which SQLite it's finding, and how to make
it use the new one....
( thanks for the help! )

On Apr 13, 9:59 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:

[NickPerkins]

Solved...by replacing the sqlite3.dll in my c:\Python25\DLLS\ with a
new one.