constraint failed message and no clue what went wrong

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

constraint failed message and no clue what went wrong

Bernd Lehmkuhl-3
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"?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: constraint failed message and no clue what went wrong

Dominique Devienne
On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <[hidden email]
> wrote:

> [...] 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).

So using a newer version might help your troubleshooting. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: constraint failed message and no clue what went wrong

Bernd Lehmkuhl-3

> Dominique Devienne <[hidden email]> hat am 23. Mai 2016 um 11:20 geschrieben:
>
>
> On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <[hidden email]
> > wrote:
>
> > [...] 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.

Microsoft Windows [Version 10.0.10586]
(c) 2015 Microsoft Corporation. Alle Rechte vorbehalten.

D:\Programmierung\NAS\Diverse Echtdaten\Schortens\2016-03-31>sqlite3 000-0-katalog-1-3730-17851-2016-03-31-08-44-03_0000_postnas_grafik.db3
SQLite version 3.12.2 2016-04-18 17:30:31
Enter ".help" for usage hints.
sqlite> begin;
sqlite> 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;
Error: constraint failed
sqlite> rollback;
sqlite> .quit

>
> So using a newer version might help your troubleshooting. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: constraint failed message and no clue what went wrong

Dominique Devienne
On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl <[hidden email]

> wrote:
>
> > Dominique Devienne <[hidden email]> hat am 23. Mai 2016 um 11:20
> geschrieben:
> > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <
> [hidden email]
> > > [...] 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?
Try manually adding two rows into t_geometrie_index using for example

  insert into t_geometrie_index values (1, ...), (1, ...)

and see if you get the same error. I'm guessing it might be coming from
this. --DD

PS: 3.13.0 just released I think, so not the latest anymore :)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: constraint failed message and no clue what went wrong

Bernd Lehmkuhl-3

> Dominique Devienne <[hidden email]> hat am 23. Mai 2016 um 13:42 geschrieben:
>
>
> On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl <[hidden email]
> > wrote:
> >
> > > Dominique Devienne <[hidden email]> hat am 23. Mai 2016 um 11:20
> > geschrieben:
> > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <
> > [hidden email]
> > > > [...] 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 :-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: constraint failed message and no clue what went wrong

Dominique Devienne
On Monday, May 23, 2016, Bernd Lehmkuhl <[hidden email]> wrote:

>
> > Dominique Devienne <[hidden email] <javascript:;>> hat am 23. Mai
> 2016 um 13:42

> 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: [...] gives me one auto_id
> with three occurrences. I should check my data more thoroughly, I guess.
> Thanks for saving my day :-)


Glad to be of help. For info, Dan improved RTREE to issue better constraint
violation errors:
 https://www.sqlite.org/src/info/3ad2531efb64b7c5
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users