spatial view derived from ordinary view -- error message

[Antonio Valanzano]

I have got the following two views:

-- view 1

CREATE VIEW vw_consultazione AS
SELECT rowid as id, "id_origine", "origine", "indirizzo", "comune_geo", "sezione_geo", "geom"
FROM "merge_geocoding"
UNION ALL
SELECT rowid + 400000 as id, "primoID" as id_origine, 'google',"formatted" as indirizzo,"comune_geo", "sezione_geo", "geom"
FROM "Google_cumulativo_class";

-- view 2

CREATE VIEW vw_consultazione_s AS
SELECT id,id_origine, origine, indirizzo, comune_geo, sezione_geo, geom
FROM vw_consultazione;

but when I try to register the view 2 into the views_geometry_columns table with

the following code

INSERT    INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
        VALUES ('vw_consultazione_s','geom','id','vw_consultazione','geom',1);   

I receive the following error message:

SQL error: FOREIGN KEY constraint failed

Could someone tell me why ?

Any help will  be appreciated.

Antonio

[a.fu...@lqt.it]

On Tue, 19 Oct 2021 09:38:54 -0700 (PDT), Antonio Valanzano wrote:
> I receive the following error message:
> SQL error: FOREIGN KEY constraint failed
>
> Could someone tell me why ?
>

Hi Antonio,

very short reply: you can't create a Spatial View on the top of
another Spatial View; what is always expected is that a Spatial
View must be based on some Spatial Table.

more elaborate reply:

INSERT INTO views_geometry_columns (
view_name, -- this is the name of your Spatial View
view_geometry, -- this is the name of the Geometry column
view_rowid, -- this is the name of the ROWID/PK column
f_table_name, -- this is the name of the mother Table
f_geometry_column, -- this is the name of the mother Geometry
read_only)
VALUES (.....);

Constraint: f_table_name and f_geometry_column are a Foreign Key
expecting to match a corresponding entry in "geometry_columns".

this obviously is not your case because you are not referencing
a genuine Spatial Table but a Spatial View, and as reported by
the error message it simply ends up in a FK violation.

ciao Sandro