On Wed, 17 Nov 2010 16:36:12 -0600, "Bernard Ertl"
<bern...@interplansystems.com> wrote:
>I'm getting a "foreign key mismatch" error with the following code:
>
>~~~
><create new database>
>PRAGMA foreign_keys = ON;
>
>CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE);
>
>CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES
>JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL);
>
>INSERT INTO JobPlans(Name) VALUES ('234234');
>
># Following line generates the error:
>INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ');
>~~~
>
> If I explicitly declare an alias for the RowID:
>
>CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE);
>
> I don't get the error. Is it not possible
> to reference the SQLite internal/default column
> for the RowID in a foreign key definition?
No it isn't. In general you can't refer to anything that is not part
of your schema.
> The online docs should be updated to reflect this.
Disputable, as this is not specific for SQLite, it's part of SQL.
The fact that ROWID is something hidden is documented well enough.
The definition:
CREATE TABLE JobPlans (
id INTEGER PRIMARY KEY NOT NULL,
Name UNIQUE
);
is physically the same as
CREATE TABLE JobPlans (
Name UNIQUE
);
anyway, so what's the problem defining the alias?
Using the alias is much more portable.
Note that the rowid alias doesn't have to be called RowID at all.
CREATE TABLE IF NOT EXISTS Tasks (
JobPlan_ID INTEGER NOT NULL
REFERENCES JobPlans(id)
ON DELETE CASCADE,
UID UNIQUE NOT NULL
);
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
