Hello,
Please consider this (working) schema and this test data, on sqlite 3.7.4:
CREATE TABLE TestTable (
id integer primary key,
name varchar,
source_id integer not null,
parent_id integer,
foreign key(parent_id) references TestTable(id)
);
PRAGMA foreign_keys=1;
INSERT INTO TestTable VALUES (1, 'Parent', 1, null);
INSERT INTO TestTable VALUES (2, 'Child', 1, 1);
INSERT INTO TestTable VALUES (3, 'Bad', 1, 5);
This schema is supposed to represent some object which can have an internal
parent/child hierarchy, enforced using the self-referential foreign key. The
first two inserts should work, and the third should fail with a foreign key
error, which it does.
Next, I wanted to enforce that the source_id between the parent and child are
also identical: that is parents and children must come from the same source.
So, I tweaked it to use a composite foreign key, and created the required UNIQUE
index:
CREATE TABLE TestTable (
id integer primary key,
name varchar,
source_id integer not null,
parent_id integer,
foreign key(source_id, parent_id) references TestTable(source_id, id)
);
CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
PRAGMA foreign_keys=1;
INSERT INTO TestTable VALUES (1, 'Parent', 1, null);
INSERT INTO TestTable VALUES (2, 'Child', 1, 1);
INSERT INTO TestTable VALUES (3, 'Bad', 1, 5);
In this situation, the third insert succeeds, in spite of it's parent id not
existing. sqlite fails to enforce the constraint.
If I tweak the schema *slightly*, only replacing 'integer primary key' with
'integer unique', I do get the expected behaviour:
CREATE TABLE TestTable (
id INTEGER UNIQUE,
name VARCHAR,
source_id INTEGER NOT NULL,
parent_id INTEGER,
FOREIGN KEY(source_id, parent_id) REFERENCES TestTable(source_id, id)
);
CREATE UNIQUE INDEX testindex ON TestTable(source_id, id);
In this case, the third insert fails as expected. However, this is not
substantively different then the second schema, but one enforces the FK properly
and the other doesn't. I looked at the opcodes for the second, but couldn't
figure out the logic error.
If it matters, I'm doing this testing on Ubuntu 11.04, with sqlite 3.7.4.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
