pragma foreign_key_check

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

pragma foreign_key_check

Mark Wagner
The documentation for foreign_key_check says I should be receiving 4
columns per violation.  I only seem to be getting 1.  Am I doing it wrong?
Perhaps it's a version issue?

Thanks

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (id int primary key, value);
CREATE TABLE t1 (id integer primary key, v references t(value));
INSERT INTO t1 VALUES(1,1);
COMMIT;
sqlite>
sqlite> pragma foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite>
sqlite> pragma foreign_key_check(t1);
Error: foreign key mismatch - "t1" referencing "t"
sqlite>
sqlite> .version
SQLite 3.20.1 2017-08-24 16:21:36
8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34
_______________________________________________
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: pragma foreign_key_check

Clemens Ladisch
Mark Wagner wrote:
> The documentation for foreign_key_check says I should be receiving 4
> columns per violation.  I only seem to be getting 1.
>
> CREATE TABLE t (id int primary key, value);
> CREATE TABLE t1 (id integer primary key, v references t(value));
> sqlite> pragma foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"

The PRAGMA does not even run; you are not getting any result.

The problem is that there is no valid foreign key constraint;
<http://www.sqlite.org/foreignkeys.html#fk_indexes> says:
| Usually, the parent key of a foreign key constraint is the primary key
| of the parent table. If they are not the primary key, then the parent
| key columns must be collectively subject to a UNIQUE constraint or
| have a UNIQUE index.


Regards,
Clemens
_______________________________________________
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: pragma foreign_key_check

Keith Medcalf
In reply to this post by Mark Wagner

Nor does there appear to be "column names" ... and this with the current tip of the trunk.

SQLite version 3.23.0 2018-03-16 07:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys=OFF;
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE t (id int primary key, value);
sqlite> CREATE TABLE t1 (id integer primary key, v references t(value));
sqlite> INSERT INTO t1 VALUES(1,1);
sqlite> COMMIT;
sqlite> pragma foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite> pragma foreign_key_check(t1);
Error: foreign key mismatch - "t1" referencing "t"
sqlite> pragma foreign_key_check(t);
sqlite> .head on
sqlite> pragma foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite> select * from pragma_foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite> .mode col
sqlite> select * from pragma_foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite> .head on
sqlite> select * from pragma_foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite> select * from pragma_foreign_key_check();
Error: foreign key mismatch - "t1" referencing "t"
sqlite> pragma compile_options;
compile_options
-------------------------
ALLOW_COVERING_INDEX_SCAN
ALLOW_URI_AUTHORITY
COMPILER=gcc-7.2.0
DEFAULT_CACHE_SIZE=262144
DEFAULT_FOREIGN_KEYS
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PROXYDIR_PERMISSI
DEFAULT_RECURSIVE_TRIGGER
DEFAULT_WAL_AUTOCHECKPOIN
DEFAULT_WAL_SYNCHRONOUS=1
ENABLE_8_3_NAMES=1
ENABLE_API_ARMOR
ENABLE_COLUMN_METADATA
ENABLE_COSTMULT
ENABLE_CURSOR_HINTS
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_LOCKING_STYLE=1
ENABLE_MEMORY_MANAGEMENT
ENABLE_MEMSYS5
ENABLE_PREUPDATE_HOOK
ENABLE_RBU
ENABLE_RTREE
ENABLE_SESSION
ENABLE_STAT4
ENABLE_STMTVTAB
EXTRA_INIT=core_init
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_ATTACHED=15
SOUNDEX
STAT4_SAMPLES=64
TEMP_STORE=2
THREADSAFE=1
USE_URI
WIN32_MALLOC
sqlite>

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Mark Wagner
>Sent: Saturday, 17 March, 2018 12:51
>To: SQLite mailing list
>Subject: [sqlite] pragma foreign_key_check
>
>The documentation for foreign_key_check says I should be receiving 4
>columns per violation.  I only seem to be getting 1.  Am I doing it
>wrong?
>Perhaps it's a version issue?
>
>Thanks
>
>sqlite> .dump
>PRAGMA foreign_keys=OFF;
>BEGIN TRANSACTION;
>CREATE TABLE t (id int primary key, value);
>CREATE TABLE t1 (id integer primary key, v references t(value));
>INSERT INTO t1 VALUES(1,1);
>COMMIT;
>sqlite>
>sqlite> pragma foreign_key_check;
>Error: foreign key mismatch - "t1" referencing "t"
>sqlite>
>sqlite> pragma foreign_key_check(t1);
>Error: foreign key mismatch - "t1" referencing "t"
>sqlite>
>sqlite> .version
>SQLite 3.20.1 2017-08-24 16:21:36
>8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34
>_______________________________________________
>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: pragma foreign_key_check

Keith Medcalf
In reply to this post by Clemens Ladisch

Right you are Clemens:

SQLite version 3.23.0 2018-03-16 07:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys=OFF;
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE t (id int primary key, value unique);
sqlite> CREATE TABLE t1 (id integer primary key, v references t(value));
sqlite> INSERT INTO t1 VALUES(1,1);
sqlite> COMMIT;
sqlite> pragma foreign_key_check;
t1|1|t|0
sqlite> .mode col
sqlite> .head on
sqlite> pragma foreign_key_check;
table       rowid       parent      fkid
----------  ----------  ----------  ----------
t1          1           t           0
sqlite> select * from pragma_foreign_key_check;
table       rowid       parent      fkid
----------  ----------  ----------  ----------
t1          1           t           0
sqlite>


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Clemens Ladisch
>Sent: Saturday, 17 March, 2018 13:50
>To: [hidden email]
>Subject: Re: [sqlite] pragma foreign_key_check
>
>Mark Wagner wrote:
>> The documentation for foreign_key_check says I should be receiving
>4
>> columns per violation.  I only seem to be getting 1.
>>
>> CREATE TABLE t (id int primary key, value);
>> CREATE TABLE t1 (id integer primary key, v references t(value));
>> sqlite> pragma foreign_key_check;
>> Error: foreign key mismatch - "t1" referencing "t"
>
>The PRAGMA does not even run; you are not getting any result.
>
>The problem is that there is no valid foreign key constraint;
><http://www.sqlite.org/foreignkeys.html#fk_indexes> says:
>| Usually, the parent key of a foreign key constraint is the primary
>key
>| of the parent table. If they are not the primary key, then the
>parent
>| key columns must be collectively subject to a UNIQUE constraint or
>| have a UNIQUE index.
>
>
>Regards,
>Clemens
>_______________________________________________
>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: pragma foreign_key_check

Mark Wagner
In reply to this post by Keith Medcalf
That's what I get for just typing in to sqlite3.  Yes, with the unique
constraint it works.  Thanks!


On Sat, Mar 17, 2018 at 12:53 PM Keith Medcalf <[hidden email]> wrote:

>
> Nor does there appear to be "column names" ... and this with the current
> tip of the trunk.
>
> SQLite version 3.23.0 2018-03-16 07:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> PRAGMA foreign_keys=OFF;
> sqlite> BEGIN TRANSACTION;
> sqlite> CREATE TABLE t (id int primary key, value);
> sqlite> CREATE TABLE t1 (id integer primary key, v references t(value));
> sqlite> INSERT INTO t1 VALUES(1,1);
> sqlite> COMMIT;
> sqlite> pragma foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> pragma foreign_key_check(t1);
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> pragma foreign_key_check(t);
> sqlite> .head on
> sqlite> pragma foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> select * from pragma_foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> .mode col
> sqlite> select * from pragma_foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> .head on
> sqlite> select * from pragma_foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> select * from pragma_foreign_key_check();
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> pragma compile_options;
> compile_options
> -------------------------
> ALLOW_COVERING_INDEX_SCAN
> ALLOW_URI_AUTHORITY
> COMPILER=gcc-7.2.0
> DEFAULT_CACHE_SIZE=262144
> DEFAULT_FOREIGN_KEYS
> DEFAULT_MMAP_SIZE=0
> DEFAULT_PAGE_SIZE=4096
> DEFAULT_PROXYDIR_PERMISSI
> DEFAULT_RECURSIVE_TRIGGER
> DEFAULT_WAL_AUTOCHECKPOIN
> DEFAULT_WAL_SYNCHRONOUS=1
> ENABLE_8_3_NAMES=1
> ENABLE_API_ARMOR
> ENABLE_COLUMN_METADATA
> ENABLE_COSTMULT
> ENABLE_CURSOR_HINTS
> ENABLE_DBSTAT_VTAB
> ENABLE_FTS3
> ENABLE_FTS3_PARENTHESIS
> ENABLE_FTS4
> ENABLE_FTS5
> ENABLE_JSON1
> ENABLE_LOAD_EXTENSION
> ENABLE_LOCKING_STYLE=1
> ENABLE_MEMORY_MANAGEMENT
> ENABLE_MEMSYS5
> ENABLE_PREUPDATE_HOOK
> ENABLE_RBU
> ENABLE_RTREE
> ENABLE_SESSION
> ENABLE_STAT4
> ENABLE_STMTVTAB
> EXTRA_INIT=core_init
> HAVE_ISNAN
> LIKE_DOESNT_MATCH_BLOBS
> MAX_ATTACHED=15
> SOUNDEX
> STAT4_SAMPLES=64
> TEMP_STORE=2
> THREADSAFE=1
> USE_URI
> WIN32_MALLOC
> sqlite>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Mark Wagner
> >Sent: Saturday, 17 March, 2018 12:51
> >To: SQLite mailing list
> >Subject: [sqlite] pragma foreign_key_check
> >
> >The documentation for foreign_key_check says I should be receiving 4
> >columns per violation.  I only seem to be getting 1.  Am I doing it
> >wrong?
> >Perhaps it's a version issue?
> >
> >Thanks
> >
> >sqlite> .dump
> >PRAGMA foreign_keys=OFF;
> >BEGIN TRANSACTION;
> >CREATE TABLE t (id int primary key, value);
> >CREATE TABLE t1 (id integer primary key, v references t(value));
> >INSERT INTO t1 VALUES(1,1);
> >COMMIT;
> >sqlite>
> >sqlite> pragma foreign_key_check;
> >Error: foreign key mismatch - "t1" referencing "t"
> >sqlite>
> >sqlite> pragma foreign_key_check(t1);
> >Error: foreign key mismatch - "t1" referencing "t"
> >sqlite>
> >sqlite> .version
> >SQLite 3.20.1 2017-08-24 16:21:36
> >8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34
> >_______________________________________________
> >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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users