Hi,
I have the following tables (simplified):
> CREATE TABLE "message" (
> "MessageId" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "CreatedTime" DATETIME NOT NULL,
> "Owner" INTEGER NOT NULL REFERENCES "user" ("UserId"),
> "PrimaryReference" INTEGER REFERENCES "message" ("MessageId") ON DELETE SET
> NULL,
> "SearchRevisionId" INTEGER REFERENCES "message_revision"
> ("MessageRevisionId"));
> CREATE TABLE "message_revision" (
> "MessageRevisionId" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "MessageId" INTEGER NOT NULL REFERENCES "message" ("MessageId") ON DELETE
> CASCADE,
> "CreatedTime" DATETIME NOT NULL,
> "Author" INTEGER NOT NULL REFERENCES "user" ("UserId"),
> "Subject" VARCHAR(255),
> "Content" MEDIUMTEXT);
message contains a message definition, message_revision contains each
revision of the message content. All message revisions link to their
message definition. A message keeps an optional link to one of the
revisions that is used when searching the messages.
So I have foreign keys from message_revision to message and the other
way around. This obviously won't work because when defining the table
message, the table message_revision isn't known yet.
In my setup scripts for other databases, I have first created the tables
alone and then added all foreign keys like this:
> ALTER TABLE "message" ADD FOREIGN KEY ("Owner") REFERENCES "user" ("UserId");
> ALTER TABLE "message" ADD FOREIGN KEY ("PrimaryReference") REFERENCES
> "message" ("MessageId") ON DELETE SET NULL;
> ALTER TABLE "message" ADD FOREIGN KEY ("SearchRevisionId") REFERENCES
> "message_revision" ("MessageRevisionId");
> ALTER TABLE "message_revision" ADD FOREIGN KEY ("MessageId") REFERENCES
> "message" ("MessageId") ON DELETE CASCADE;
> ALTER TABLE "message_revision" ADD FOREIGN KEY ("Author") REFERENCES "user"
> ("UserId");
But SQLite doesn't understand this statement.
So how am I supposed to solve this now?
I had used the manual trigger solution before, i.e. creating the
triggers by hand that enforce referential integrity. This was getting
cumbersome so I decided to give SQLite's recent foreign key support a
try. It sounded pretty complete by the documentation, but seems very
limited from this view.
--
Yves Goergen "LonelyPixel" <[email protected]>
Visit my web laboratory at http://beta.unclassified.de
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
