> Dominique Devienne <ddevienne at gmail.com> hat am 23. Mai 2016 um 13:42
> geschrieben:
>
>
> On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl <bernd.lehmkuhl at
> mailbox.org
> > wrote:
> >
> > > Dominique Devienne <ddevienne at gmail.com> hat am 23. Mai 2016 um 11:20
> > geschrieben:
> > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <
> > bernd.lehmkuhl at mailbox.org
> > > > [...] What might cause a "constraint failed" message following
> > this command: [...]
> > >
> > > Which version of SQLite? More recent ones tell you which constraint
> > failed,
> > > when they are named, which yours are (a good thing IMHO).
> >
> > Most recent one - 3.12.2. Unfortunately without any name.
> > [...]
>
>
> /*** t_geometrie_index ***/
> CREATE VIRTUAL TABLE t_geometrie_index USING rtree(
> id,
> [...]
>
> sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
> > ...> SELECT
> > ...> t.auto_id, [...]
>
> ...> FROM
> > ...> (
> > ...> SELECT
> > ...> k.id, [...]
> > ...> FROM
> > ...> t_geometrie_knoten k
> > ...>
> > ...> UNION ALL <<<<<<
> > ...>
> > ...> SELECT
> > ...> p.id, [...]
> > ...> FROM
> > ...> t_geometrie_punkte p
> > ...> GROUP BY
> > ...> p.id
> > ...> ) sub JOIN t_geometrie_typ t
> > ...> ON sub.id = t.id;
> > Error: constraint failed
> > sqlite> rollback;
> > sqlite> .quit
> >
>
> OK, was worth a shot. I had a feeling it might be related to the RTREE
> vtables.
>
> Never used RTREE myself, in SQLite, although I know what this is.
> Could it be your you "knoten" and "punkte" tables have values with the same
> IDs?
Gotcha! Thanks. Even though I claimed having checked that auto_id is unique in
that query, you just proofed me wrong:
SELECT
auto_id,
COUNT(*)
FROM
(
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
)
GROUP BY
auto_id
HAVING
COUNT(*) > 1
gives me one auto_id with three occurrences. I should check my data more
thoroughly, I guess.
Thanks for saving my day :-)
