Re: [sqlite] Using foreign key reference on RowID

[Bernard Ertl]

Wow.  Thanks.  I see now that this is mentioned in the docs on the page for
the VACUUM statement.  It really should be mentioned on the CREATE TABLE
page also where the rowid is explained.  This is important information for
people who are learning SQLite and trying to figure out how to design their
database!


Jay A. Kreibich-2 wrote:
> 
> On Wed, Nov 17, 2010 at 04:36:12PM -0600, Bernard Ertl scratched on the
> wall:
> 
>> Is it not possible to reference the SQLite
>> internal/default column for the RowID in a foreign key definition?
> 
>   Even if you could, you don't want to do this.  
>   
>   Unless you define an ROWID alias (i.e. an INTEGER PRIMARY KEY column)
>   ROWID values are not preserved across vacuums or dumps.
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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-tp30246958p30248863.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

Re: [sqlite] Using foreign key reference on RowID

[Bernard Ertl]

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"
>  wrote:
> 
>>I'm getting a "foreign key mismatch" error with the following code:
>>
>>~~~
>>
>>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

Re: [sqlite] Using foreign key reference on RowID

[Jay A. Kreibich]

On Wed, Nov 17, 2010 at 04:36:12PM -0600, Bernard Ertl scratched on the wall:

> Is it not possible to reference the SQLite
> internal/default column for the RowID in a foreign key definition?

  Even if you could, you don't want to do this.  
  
  Unless you define an ROWID alias (i.e. an INTEGER PRIMARY KEY column)
  ROWID values are not preserved across vacuums or dumps.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using foreign key reference on RowID

[Kees Nuyt]

On Wed, 17 Nov 2010 16:36:12 -0600, "Bernard Ertl"
 wrote:

>I'm getting a "foreign key mismatch" error with the following code:
>
>~~~
>
>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