Hello Keith,
On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf wrote:
>
> Unrelated, but are you sure that you want the albums "id int primary key"
> and did not happen to misspell "integer" so that the declaration should be
> "id integer primary key". In the former case, id is an integer that just
> happens to be unique (ie, "id int primary key" is the same as "id integer
> unique") and not an explicitly named alias for the rowid (which requires
> correct spelling of the phrase "integer primary key").
>
I though "int" was the same as "integer" (
https://www.sqlite.org/datatype3.html).
Thanks.
> Second unrelated, do you not want an affinity for the album_id column in
> rates? Should not you have declared it as "album_id integer references
> albums(id) on delete cascade"?
>
Correct.
Third unrelated, do not forget to create an index on the foreign key (as in
> "CREATE INDEX idxRates_album_id on rates (album_id)" for example).
>
Sure.
Fourth unrelated, do you want the title and comment_text to be case
> sensitive or should they have COLLATE NOCASE?
>
No.
> As to the issue with the updated table rename, you can either use a
> version of sqlite3 that does not have the alter table rename updates, or
> for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid
> using the new "change the table names in triggers etc" features added in
> 3.25.0 so that you can continue to use the old method of just "substituting
> tables".
>
Indeed, use the pragma, thanks! https://www.sqlite.org/pragma.html#toc
using pragma, the following script output 5, which is expected result.
drop table if exists albums;
drop table if exists rates;
--
create table albums (id integer primary key, title text, score int);
create table rates (album_id integer references albums(id) on delete
cascade, score int);
create trigger test after insert on rates begin update albums set
score=new.score where id=new.album_id; end;
--
insert into albums (id, title) values (1, 'Cheap Thrills');
--
begin;
pragma legacy_alter_table=ON;
drop table if exists new_albums;
create table new_albums (id int primary key, title text not null, score
int);
insert into new_albums (id, title, score) select id, title, score from
albums;
drop table albums;
alter table new_albums rename to albums;
pragma legacy_alter_table=OFF;
end;
--
insert into rates values (1, 5);
select score from albums;
When I comment the pragma, I have
Error: near line 16: error in trigger test: no such table: main.albums
Error: near line 20: no such table: main.albums
Error: near line 21: no such table: albums
It is a resolution for the problem I faced : add a constraint to colum of a
table referenced in a trigger.
Do you believe ?
a) https://www.sqlite.org/lang_altertable.html#otheralter could be updated.
b) rename A to B should not fail in the face of a trigger referencing B.
?
, Thierry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
