My System:

$ python
Python 2.7.5 (default, May 15 2013, 22:43:36) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.6.21'

Test Script:

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON')
fk = (conn.execute("PRAGMA foreign_keys").fetchone()[0])
print 'version = %s, foreign keys = %r' % (sqlite3.sqlite_version, bool(fk))
if not fk:
  raise Exception('No foreign keys!?')

c = conn.cursor()
c.executescript('''
create table if not exists main.one (resource_id TEXT PRIMARY KEY, data TEXT);
create table if not exists main.two (node_id INTEGER PRIMARY KEY, data TEXT);
create table if not exists main.mapping (node_id INTEGER REFERENCES two, resource_id TEXT REFERENCES one);
insert into main.one(resource_id, data) values('A', 'A one thing');
insert into main.two(node_id, data) values(1, 'A two thing');
insert into main.mapping(resource_id, node_id) values('A', 1);
insert into main.one(resource_id, data) values('B', 'Another one thing');
insert into main.two(node_id, data) values(2, 'Another two thing');
insert into main.mapping(resource_id, node_id) values('B', 2);
insert into main.one(resource_id, data) values('C', 'Yet another one thing');
''')
for tbl in 'one', 'two', 'mapping':
  print 'TABLE main.%s:\n%s\n' % (tbl, '\n'.join(repr(r) for r in c.execute('select * from main.%s' % tbl).fetchall()))

del_cmd = """delete from main.one where resource_id='B'"""
print 'Attempting: %s' % (del_cmd,)
try:
  c.execute(del_cmd)
except Exception, e:
  print 'Failed to delete: %s' % e

cmd = """delete from main.one where resource_id='C'"""
print 'Attempting: %s' % (cmd,)
c.execute(cmd)

cmd = """delete from main.mapping where resource_id='B' AND node_id=2"""
print '\nAttempting: %s' % (cmd,)
c.execute(cmd)

for tbl in 'one', 'two', 'mapping':
  print 'TABLE main.%s:\n%s\n' % (tbl, '\n'.join(repr(r) for r in c.execute('select * from main.%s' % tbl).fetchall()))

print 'Attempting: %s' % (del_cmd,)
c.execute(del_cmd)


This fails with "sqlite3.IntegrityError: foreign key constraint failed".  Original report comes from SO: http://stackoverflow.com/questions/9342763/sqlite3-foreign-keys-remembered 

The proposed solution (to upgrade sqlite) is not possible on Windows as it comes bundled with Python. 
So please update the bundled sqlite version where this bug is solved.