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").
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"? 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). Fourth unrelated, do you want the title and comment_text to be case sensitive or should they have COLLATE NOCASE? 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". --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Thierry Henrio >Sent: Wednesday, 3 October, 2018 16:43 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] alter table, other alter category, fails in >presence of trigger on 3.25.2 > >Hello, > >I want to report a bug, I checked >https://www.sqlite.org/src/rptview?rn=8 >and did not find the same? > >Let schema be: > >sqlite> .schema >CREATE TABLE albums (id int primary key, title text, score int); >CREATE TABLE rates (album_id references albums(id) on delete cascade, >comment text, score int); >CREATE TRIGGER test after insert on rates begin update albums set >score=new.score where id=new.album_id; end; > >And I want to make albums.title not null. >This falls into >https://www.sqlite.org/lang_altertable.html#otheralter. > >sqlite> BEGIN; >sqlite> CREATE TABLE new_albums (id int primary key, title text not >null, >score int); >sqlite> INSERT INTO new_albums (id, title, score) SELECT id, title, >score >FROM albums; >sqlite> DROP TABLE albums; >sqlite> ALTER TABLE new_albums RENAME TO albums; >Error: error in trigger test: no such table: main.albums > >Expected behavior is last alter is ok. > >This is in 3.25.2 > >sqlite> select sqlite_version(); >3.25.2 > >Cheers, Thierry >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
