Dear list,
having the following database schema:
/*** t_geometrie_typ ***/
CREATE TABLE t_geometrie_typ(
auto_id INTEGER PRIMARY KEY AUTOINCREMENT, -- automatically generated id as
link to the r*Tree index
id TEXT UNIQUE NOT NULL,
typ TEXT COLLATE NOCASE NOT NULL,
objektart TEXT NOT NULL,
crs TEXT,
qualitaetsangabenDatenerhebung TEXT);
CREATE INDEX i_geometrie_typ_crs
ON t_geometrie_typ (crs, id);
CREATE INDEX i_geometrie_typ_objektart
ON t_geometrie_typ (objektart, id);
/*** t_geometrie_umring ***/
CREATE TABLE t_geometrie_umring(
id TEXT,
umringnummer INTEGER NOT NULL,
typ TEXT NOT NULL COLLATE NOCASE,
CONSTRAINT fk_geometrie_umring_id FOREIGN KEY (id)
REFERENCES t_geometrie_typ (id) ON DELETE CASCADE,
CONSTRAINT c_geometrie_umring_typ CHECK (typ IN ('Exterior', 'Interior')),
CONSTRAINT u_geometrie_umring UNIQUE (id, umringnummer));
/*** t_geometrie_punkte ***/
CREATE TABLE t_geometrie_punkte(
id TEXT,
x REAL NOT NULL,
y REAL NOT NULL,
bulge REAL NOT NULL,
umringnummer INTEGER,
laufendenummer INTEGER NOT NULL ,
CONSTRAINT fk_geometrie_punkte_id FOREIGN KEY (id)
REFERENCES t_geometrie_typ (id) ON DELETE CASCADE,
CONSTRAINT u_geometrie_punkte UNIQUE (id, umringnummer, laufendenummer));
/*** t_geometrie_knoten ***/
CREATE TABLE t_geometrie_knoten(
id TEXT NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL,
CONSTRAINT pk_geometrie_knoten PRIMARY KEY (id, x, y),
CONSTRAINT fk_geometrie_knoten_id FOREIGN KEY (id)
REFERENCES t_geometrie_typ (id) ON DELETE CASCADE);
/*** t_geometrie_index ***/
CREATE VIRTUAL TABLE t_geometrie_index USING rtree(
id,
xmin,
xmax,
ymin,
ymax);
What might cause a "constraint failed" message following this command:
INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
SELECT
t.auto_id,
sub.xmin,
sub.xmax,
sub.ymin,
sub.ymax
FROM
(
SELECT
k.id,
k.x AS xmin,
k.x AS xmax,
k.y AS ymin,
k.y AS ymax
FROM
t_geometrie_knoten k
UNION ALL
SELECT
p.id,
MIN(p.x),
MAX(p.x),
MIN(p.y),
MAX(p.y)
FROM
t_geometrie_punkte p
GROUP BY
p.id
) sub JOIN t_geometrie_typ t
ON sub.id = t.id;
I don't see any dependencies on the r*Tree table t_geometrie_index.
Executing just the SELECT part of the query I checked that all xmin's are less
or equal to the xmax's, the same holds true for the y-values. There are no
double auto_id's.
So what might cause the error message "constraint failed"?
