As someone who just started using SQLite without any previous background in SQL, it was confusing to me. I did a search on nabble through this mailing list and see now that I'm not the first person to ask about this issue. IMO, it would be helpful to people new to SQLite to mention this in the docs on the foreign key support page:
http://www.sqlite.org/foreignkeys.html It's not clear from the CREATE TABLE page in the docs: http://www.sqlite.org/lang_createtable.html if there are any performance issues or other considerations in defining an alias to the rowid. The text there doesn't really offer any reason to someone new to the system to use an alias. Seems like a duplication of work for no benefit (because the benefits aren't clearly explained). Maybe this text should be updated so people are encouraged to use an alias instead of the 'hidden' column. Kees Nuyt wrote: > > 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 > > -- View this message in context: http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248826.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
