Overview ( the reasons why ) Every project, in some form, creates some rules or specifications that it will follow
For Spatialite , Simple Feature Access (Part 2) conformance is rule #1

6.1.2 Identification of feature tables and geometry columns [ geometry_columns ]

f_table_name : the identity of the feature table of which this Geometry Column is a member

f_geometry_name : the name of the Geometry Column

geometry_type : the type of Geometry for the Geometry column

coord_dimension : the coordinate dimension for the Geometry Column

srid : the spatial reference system ID (SRID) for the Geometry Column

6.2 Architecture — SQL implementation using Geometry Types

geometry_columns : table describes the available feature tables and their Geometry properties

spatial_ref_sys : table describes the coordinate system and transformations for Geometry

We must have (at least) 2 tables: geometry_columns and spatial_ref_sys

Every geometry-column must be registered in the geometry_columns table

Every geometry stored must have the srid , geometry_type and coord_dimension that are defined in the geometry_columns table

These restrictions must be enforced to conform to the OGC-SFS specification. Note :
Since Spatialite is an extension of SQLite
SQLite will not know of ( nor care about ) these restrictions !

CREATE , DROP , SELECT , INSERT , UPDATE and DELETE

will be acted upon as any normal TABLE or VIEW . From the viewpoint of Spatialite : there is a difference. Only when, for a

SpatialTable : at least one geometry-column has been defined in geometry_columns

SpatialView : only one geometry-column has been defined in views_geometry_columns

will it be treated as a SpatialTable or a SpatialView . Long answer :
A SpatialTable is initialy created as a normal SQLite -TABLE, but without a geometry-column:
(after removing the VIEW created in Creation of the VIEW admin_cities ) -- DROP the VIEW we created in Chapter 4
DROP VIEW IF EXISTS admin_cities;
-- DROP (possible) previous versions of our masterpiece
DROP TABLE IF EXISTS admin_cities;
CREATE TABLE admin_cities
-- the id of the City
id_city INTEGER NOT NULL PRIMARY KEY,
-- the name of the City
name_city TEXT NOT NULL,
-- Population of City, as whole persons
population_city INTEGER DEFAULT 0,
-- id of the Province the City belongs to
id_province INTEGER DEFAULT 0,
-- name of the Province the City belongs to
name_province TEXT DEFAULT '',
-- The Car Plate of the Province the City belongs to
car_plate_code TEXT DEFAULT '',
-- id of the Region the Province belongs to
id_region INTEGER DEFAULT 0,
-- name of the Region the Province belongs to
name_region TEXT DEFAULT '',
-- id of Country the Region belongs to [default: 39, Italy]
id_country INTEGER DEFAULT 39,
-- name of Country the Region belongs to [default: Italy]
name_country TEXT DEFAULT 'Italy'
At this point in time, ' admin_cities ' is still a normal SQLite -TABLE. Now we will add a geometry-column called ' geom_city ' to the SQLite -TABLE ' admin_cities ',
with all the information needed for geometry_columns ( srid , geometry_type and coord_dimension ) Now ' admin_cities ' is a SpatialTable SELECT
AddGeometryColumn('admin_cities', 'geom_city', 32632, 'MULTIPOLYGON', 'XY');
The following is true for AddGeometryColumn :

checking is done if the TABLE exist and supports ROWID

checking is done if the srid , geometry_type and coord_dimension parameters are valid

The Database is NOT a FDO or GeoPackage

With a ALTER TABLE command a Column will be added

With a INSERT INTO geometry_columns command the names of the feature table and column ,
as well as the srid , geometry_type and coord_dimension will be inserted into geometry_columns

Creation of entries for interal tables geometry_columns_ auth / fields_infos / statistics

The following is true RecoverGeometryColumn :

any previous entry in geometry_columns will be removed

otherwise the same as AddGeometryColumn , with the exception of the ALTER TABLE command,
since it is assumed that the geometry-column already exists.

With that, the OGC-SFS conditions for the geometry_columns entries are complete. For this, the SQLite concept of TRIGGER s will be used The following is true for both AddGeometryColumn and RecoverGeometryColumn :

checking is done if older (version < 2.4) TRIGGER s exist that need to be replaced

adding of TRIGGER s, based on Database version (Legacy, version ≥ 4.0), for the following events :

BEFORE INSERT

BEFORE UPDATE

checking for valid geometry_type and srid as found in geometry_columns , using the internal function GeometryConstraints() . Note :
These TRIGGER s are the cause of the ' violates Geometry constraint [geom-type or SRID not allowed] ' errors
( I find these ' I know what it is, but will not tell you ' messages dreadful )
In most cases they are caused by one (or more) of the following:

an invalid geometry-type (a POLYGON instead of a MULTIPOLYGON , use CastToMulti() to resolve)

an invalid dimension (a POINT XY instead of POINT Z ), use CastToXYZ() to resolve)

an invalid srid ( 0 instead of 4326 , use SetSRID() to resolve)

or all of the above. With that, the OGC-SFS conditions for the srid , geometry_type and coord_dimension entries are complete. The SpatialTable can now be filled with: INSERT INTO admin_cities
(id_city, name_city, population_city, id_province, name_province, car_plate_code, id_region, name_region, geom_city)
SELECT
-- the id of the City
c.pro_com AS id_city,
-- the name of the City
c.comune AS name_city,
-- Insure whole numbers ('Hanged, drawn and quartered' has been abolished, no longer need for quarter sums)
CAST (c.pop_2011 AS INTEGER) AS population_city,
-- id of Province the City belongs to
c.cod_pro AS id_province,
-- name of Province the City belongs to
p.provincia AS name_province,
-- The Car Plate of the Province the City belongs to
p.sigla AS car_plate_code,
-- id of region the Province belongs to
c.cod_reg AS id_region,
-- id of region the Province belongs to
r.regione AS name_region,
-- The Geometry ofthe City
c.geometry AS geom_city
-- contains the columns 'cod_pro' and 'cod_reg'
FROM com2011_s AS c
-- contains the columns 'cod_pro' and 'cod_reg'
JOIN prov2011_s AS p USING (cod_pro)
-- contains the column and 'cod_reg'
JOIN reg2011_s AS r USING (cod_reg);

dropping columns is not supported .

Starting with SQLite 3.25.0, the renaming of a column is supported .

Spatialite does allow you to revert what it created with AddGeometryColumn or RecoverGeometryColumn commands
With the DiscardGeometryColumn command, the entries in the geometry_columns TABLE and the created TRIGGER s will be removed,
but the column 'geom_city', with its data in the 'admin_cities' TABLE will still exist. Let us assume, you forgot to add ' Z ' support to the geometries. The following would be possible: -- remove the TRIGGERs checking for MULTIPOLYGON XY geometry-type
SELECT DiscardGeometryColumn('admin_cities','geom_city');
-- Cast to Z: no 'violates Geometry constraint [geom-type or SRID not allowed]' error
UPDATE admin_cities SET geom_city=CastToXYZ(geom_city);
-- redefine as the MULTIPOLYGON XYZ geometry-type (Z-Values are set to 0)
SELECT RecoverGeometryColumn('admin_cities', 'geom_city', 32632, 'MULTIPOLYGON', 'XYZ');
-- always a good idea [will fail if 'geom_city' is not properly registered]
SELECT UpdateLayerStatistics('admin_cities', 'geom_city');
The most effective way to clean up SpatialTable s that are no longer needed is:

SELECT DropGeoTable('admin_cities');

This will have the same effect as a DROP TABLE IF EXISTS admin_cities;

removing any entries in the geometry_columns TABLE and the created TRIGGER s

removing any other dependencies (such as any registered SpatialView s)

Conclusions :

SQLite commands should be avoided on SpatialTable s, when Spatialite versions for the same task exist

trust the Spatialite implementations, as you would (hopefully) avoid ' the reinvention of the Wheel ' in your projects

Short answer :
after a valid VIEW has been created with : CREATE VIEW and registered in the views_geometry_columns TABLE For SQLite , a valid VIEW is considered to be:

when used columns of the VIEW are defined . See SQLite : CREATE VIEW syntax explicitly declaring columns for further details For Spatialite , a valid SpatialView is considered to be:

when the geometry column of the underlining TABLE has been created with AddGeometryColumn or RecoverGeometryColumn
thus, a geometry column cannot be dynamically created

only 1 geometry column is supported. Any other geometry column , defined in the VIEW, will be treated as a BLOB

a column representing the Primary-Key of the underlining TABLE containing the geometry column must be declared in the VIEW and as the view_rowid value in the views_geometry_columns entry

Conclusion : No joy will fall upon you when:

the SpatialView is not (properly) registered in the views_geometry_columns TABLE

your geometry column is dynamically created ( ST_Transform(geometry,4326) would be a dynamically created geometry)

the Primary-Key of the underlining TABLE is based on more than 1 column (and thus cannot be contained as a value of the view_rowid in the views_geometry_columns entry)

the Primary-Key is defined as ROWID and the underlining TABLE has be defined with WITHOUT ROWID (otherwise considered a valid entry)

the SpatialView has been defined as writable (i.e. readonly=0 ) and no corresponding TRIGGER s exist (or do not work correctly)

(end of Short answer ) Long answer :
A typical registration in the views_geometry_columns TABLE will look like this: INSERT INTO views_geometry_columns
(view_name,view_geometry,view_rowid,f_table_name,f_geometry_column,read_only)
VALUES ('middle_earth_farthings','eur_polygon','id_rowid','middle_earth_polygons','eur_polygon',0);

View-Name : Name used with the CREATE VIEW statement

Geometry-Name : defined Column-Name in the VIEW

Primary-Key : column representing the Primary-Key of the underlining TABLE

underlining TABLE-Name : used in the FROM portion of the CREATE VIEW statement

geometry column : used in the underlining TABLE

readonly (0 or 1) : defining if the VIEW is expected to be READONLY or if corresponing TRIGGER s exist for one or all of the following:

INSTEAD OF INSERT defining how DATA should be added to the underlining TABLE

INSTEAD OF UPDATE defining how DATA should be changed in the underlining TABLE

INSTEAD OF DELETE defining how DATA should be removed from the underlining TABLE

Note: Reading applications (such as QGIS) should check for the existance of such TRIGGERs to determine the Adding, Modifing and Delete capabilities when readonly=0
As of QGIS 3.1 , when using the Spatialite-Provider: these TRIGGER checks are not being done, but run correctly when the TRIGGERs exist.
As of Gdal-Ogr 2.4.0 : writable SpatialView s are not supported. Will allways be treated as READONLY , despite existing TRIGGER support.
Sample Triggers for SpatialView Sample Triggers:
DROP VIEW IF EXISTS 'middle_earth_farthings'; CREATE VIEW IF NOT EXISTS 'middle_earth_farthings' (id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01, id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon) AS SELECT id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01, id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon FROM "middle_earth_polygons" WHERE ( -- 4=farthings, counties, provinces (admin_level IN (4)) ORDER BY name;The following TRIGGER s support the SpatialView registered in the views_geometry_columns TABLE above: INSTEAD OF INSERT defining how DATA should be added to the underlining TABLE CREATE TRIGGER IF NOT EXISTS 'vw_ins_middle_earth_farthings' INSTEAD OF INSERT ON "middle_earth_farthings" BEGIN INSERT OR REPLACE INTO "middle_earth_polygons" (id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01, id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon) VALUES NEW.id_rowid, NEW.id_admin, NEW.name, -- maps=1, continents=2, realms=3, farthings=4 CASE WHEN NEW.rule_type IS NULL THEN 3 ELSE NEW.rule_type END, NEW.valid_since, NEW.valid_until, NEW.id_belongs_to, NEW.belongs_to_01, NEW.id_belongs_to_02, NEW.belongs_to_02, CASE WHEN NEW.admin_level IS NULL THEN 4 ELSE NEW.admin_level END, NEW.order_selected, CASE WHEN NEW.rule_text IS NULL THEN 'farthings' ELSE NEW.rule_text END, CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Length(ST_LinesFromRings(NEW.eur_polygon)) ELSE 0 END, CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Area(NEW.eur_polygon) ELSE 0 END, NEW.notes, NEW.text, CastToMultiPolygon(NEW.eur_polygon) END; Notes :

meters_length will be calculated using the Spatial-Functions ST_Length(ST_LinesFromRings(..)) and returns the length of the POLYGON External-Ring

meters_area will be calculated using the Spatial-Function ST_Area and returns the area of the POLYGONs External-Ring minus the Internal-Rings

admin_level if not overided , the default value for this VIEW ( 4 ) will be used

eur_polygon will be casted to a MULTIPOLYGON, using the Spatial-Function CastToMultiPolygon

INSTEAD OF UPDATE defining how DATA should be changed in the underlining TABLE CREATE TRIGGER IF NOT EXISTS 'vw_upd_middle_earth_farthings' INSTEAD OF UPDATE OF id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01, id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon ON "middle_earth_farthings" BEGIN UPDATE "middle_earth_polygons" id_rowid = NEW.id_rowid, id_admin = NEW.id_admin, name = NEW.name, admin_level = CASE WHEN NEW.admin_level IS NULL THEN 4 ELSE NEW.admin_level END, valid_since = NEW.valid_since, valid_until = NEW.valid_until, id_belongs_to = NEW.id_belongs_to, belongs_to_01 = NEW.belongs_to_01, id_belongs_to_02 = NEW.id_belongs_to_02, belongs_to_02 = NEW.belongs_to_02, order_selected = NEW.order_selected, -- maps=1, continents=2, realms=3, farthings=4 rule_type = CASE WHEN NEW.rule_type IS NULL THEN 3 ELSE NEW.rule_type END, rule_text = CASE WHEN NEW.rule_text IS NULL THEN 'farthings' ELSE NEW.rule_text END, meters_length = CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Length(ST_LinesFromRings(NEW.eur_polygon)) ELSE 0 END, meters_area = CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Area(NEW.eur_polygon) ELSE 0 END, notes = NEW.notes, text = NEW.text, eur_polygon = CastToMultiPolygon(NEW.eur_polygon) WHERE id_rowid = OLD.id_rowid; END;Different syntax, performing the same tasks as above,using the column representing the Primary-Key of the underlining TABLE in the WHERE portion of the statement. INSTEAD OF DELETE defining how DATA should be removed from the underlining TABLE CREATE TRIGGER IF NOT EXISTS 'vw_del_middle_earth_farthings' INSTEAD OF DELETE ON middle_earth_farthings BEGIN -- the primary key known to the view must be used ! DELETE FROM middle_earth_polygons WHERE id_rowid = OLD.id_rowid; There are no safety margins at all, and even the slightest error will cause immediate death. Anyway an exceptionally able (and lucky) acrobat could sometimes survive unharmed. Alessandro Furieri, Firenze, Toscana spatialite-date: 7.4.17 [2015-08-07] A Writable- SpatialView that has no TRIGGERs is What is allowed and what is not allowed

TABLE : may be DROPed or renamed (with the exception of sqlite3 and Spatialite/GeoPackage internal/admin TABLEs)

VIEW : may be DROPed (with the exception of Spatialite/GeoPackage internal/admin TABLEs)

VIEW : may not be renamed

COLUMN : may not be DROPed

COLUMN of a VIEW : may not be renamed

COLUMN of a TABLE : may be renamed (with the exception of sqlite3 and Spatialite/GeoPackage internal/admin TABLEs)

Rename of TABLE :
SQLite activities during a ALTER TABLE "main"."middle_earth_admin" RENAME TO "center_earth_admin" command

VIEW s and TRIGGER s that use this TABLE will be searched for

each COLUMN used will be checked if it exists in the given TABLE

if no errors are found the old Table-Name will be replaced with the new Table-Name

if no errors are found the sqlite_master entry will be replaced with the new Table-Name

if errors are found all VIEW or TRIGGER changes will be reverted to its previous values

Rename of TABLE-COLUMN:
SQLite activities during a ALTER TABLE "main"."center_earth_admin" RENAME COLUMN "admin_type" TO "admin_level"; command

VIEW s and TRIGGER s that use this TABLE will be searched for

each COLUMN used will be checked if it exists in the given TABLE

if no errors are found the old Column-Name will be replaced with the new Column-Name

if no errors are found the sqlite_master CREATE TABLE command will be replaced using the new Column-Name

if errors are found all VIEW or TRIGGER changes will be reverted to its previous values

Common Errors :
TRIGGER s for a VIEW usees a COLUMN that is not defined in the VIEW , but is defined in the underlining TABLE error in trigger vw_ins_middle_earth_admin_general: no such column: NEW.id_belongs_to :
Resolve by : adding id_belongs_to to the VIEW Column-Definition   A COLUMN of a VIEW has not been explicitly declared in the CREATE VIEW statement error in trigger vw_ins_middle_earth_admin_general after rename: no such column: NEW.admin_type : Resolve by : adding id_belongs_to to the VIEW Column-Definition
SQLite CREATE VIEW syntax explicitly declaring columns :
The explicit declaration of Columns in the CREATE VIEW is (officialy) optional.
Inofficialy, any Column not explicitly defined is considered undefined and SQLite will attempt to resolve the old name by looking inside the underlining TABLE
(which at this point already contains the new Column-Name), which is not found causing the error.

if this makes sense to you: good

if not: welcome too the club

To avoid this problem/trap altogether, simply define the column-name to be used in the VIEW in the CREATE VIEW statement.   To insure that a constant, proper column resolvement, VIEWs should be defined in a similar way as an INSERT command where a sub-set of columns with values is done:
INSERT INTO gcp_master
(name, longitude,latitude)
SELECT
name, longitude,latitude
FROM populated_places
WHERE name LIKE "roma,%"
As a VIEW , the command would then look like this: CREATE VIEW gcp_master_view
(name, longitude,latitude) AS
SELECT
name, longitude,latitude
FROM populated_places
WHERE name LIKE "roma,%"
CREATE VIEW gcp_master_view AS
SELECT
name AS name, longitude AS longitude,latitude AS latitude
FROM populated_places
WHERE name LIKE "roma,%"
Inside the Spatialite-Library , this method is used.
Both Methods should insure that SQLite will find the old Column-Name used in the VIEW during renaming of a column of the underlining TABLE . Note :
After a Rename of the Column-Name of the underlining TABLE , the Column-Name of the VIEW will remain unchanged : ALTER TABLE "main"."populated_places" RENAME COLUMN "latitude" TO "position_y"
ALTER TABLE "main"."populated_places" RENAME COLUMN "longitude" TO "position_x"
The resulting CREATE VIEW statement will then look something like this: CREATE VIEW gcp_master_view AS
SELECT
name AS name, "position_x" AS longitude,"position_y" AS latitude
FROM populated_places
WHERE name LIKE "roma,%"
Conclusion :
Just because an optional parameter is not being used, does not mean that the Sql-Statement will always work without it.
SQLite will try to resolve your problem for you ....
... but if it occasionally fails to resolve your problem for you ...
... it is still your problem to resolve. Since SpatiaLite is an extension of SQLite , all TABLE/COLUMN rename activities are based on all the conditions that apply to the ALTER TABLE logic.

DropTable : will DROP any Spatial or non-Spatial TABLE or VIEW , dealing with any needed Administration housekeeping tasks

RenameTable : will rename any non-Spatial TABLE and Spatial- TABLE only within the main Database, dealing with any needed Administration housekeeping tasks

RenameColumn : will rename a COLUMN within any non-Spatial TABLE and for a Spatial- TABLE within the main Database, dealing with any needed Administration housekeeping tasks

Note s:
DropTable replaces the previous DropGeoTable command, which has been deprecated and therefore should no longer be used.
A SQLite version ≥ 3.25.0 is needed for RenameTable and RenameColumn , but cannot be used for any Virtual Tables or Views
Views will be adapted when RenameTable and RenameColumn has been used for any any Spatial or non-Spatial TABLE
(The Views Column-Names will remain unchanged. For more on this topic see: SQLite : CREATE VIEW syntax explicitly declaring columns )
Administration housekeeping tasks such as: SpatialIndex, Triggers, Metadata and Statistics definition maintainance for Spatialite , RasterLite2 , GeoPackage and Fdo
For Rasters : RasterLite2 ( raster_coverage ) and GeoPackage ( tiles ):

DropTable : will DROP the raster_coverage or tiles , dealing with any needed Administration housekeeping tasks

RenameTable : will rename the raster_coverage or tiles , dealing with any needed Administration housekeeping tasks

RenameColumn : will return a not supported error, since there is no appropriate action to take

General :

Administration TABLE s: for Spatialite , RasterLite2 , Topology , GeoPackage and Fdo cannot be DROP ed or renamed in any way, preventing any damage

non-Spatial TABLE s: will be handeled in the same way as any combination of the ALTER TABLE command

The ALTER TABLE command: should not be used in any Database containing Spatialite , RasterLite2 , Topology , GeoPackage or Fdo data