The phrase "integer primary key" is the "wingardium leviosa" of the sqlite world. It must be pronouced correctly, lest you end up with a buffalo on your chest.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thierry Henrio Gesendet: Donnerstag, 04. Oktober 2018 11:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] alter table, other alter category, fails in presence of trigger on 3.25.2 Hello Keith, On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf <kmedc...@dessus.com> 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
