SQLite Forum

BUG: pragma_foreign_key_check() does not accept an argument
Login

BUG: pragma_foreign_key_check() does not accept an argument

(1.1) By Jonas Rodrigues (jxonas) on 2020-07-02 23:08:31 edited from 1.0 [link] [source]

Hi there!

should pragma_foreign_key_check behave like pragma_foreign_key_list? I mean, pragma foreign_key_check(...) being equivalent to select * from pragma_foreign_key_check(...).

I'm having some problems getting it to work. See the interaction below:

SQLite version 3.33.0 2020-07-02 17:05:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo(x integer primary key);

sqlite> create table bar(y integer references foo);

sqlite> pragma foreign_key_list('bar');

id  seq  table  from  to  on_update  on_delete  match
--  ---  -----  ----  --  ---------  ---------  -----
0   0    foo    y         NO ACTION  NO ACTION  NONE 

sqlite> select * from pragma_foreign_key_list('bar');

id  seq  table  from  to  on_update  on_delete  match
--  ---  -----  ----  --  ---------  ---------  -----
0   0    foo    y         NO ACTION  NO ACTION  NONE 

sqlite> pragma foreign_key_check('bar');
-- Ok

sqlite> select * from pragma_foreign_key_check('bar');
Error: too many arguments on pragma_foreign_key_check() - max 0

(2.4) By Larry Brasfield (LarryBrasfield) on 2020-07-03 00:18:32 edited from 2.3 in reply to 1.0 [link] [source]

This appears to be a bug.

According to the Pragma Functions doc: "The PRAGMA argument and schema, if any, are passed as arguments to the table-valued function." Clearly, pragma_foreign_key_check() is one of the table-valued functions because, if no argument is provided, it can be SELECT'ed from and returns results similar to the corresponding pragma statement. Also clear is that the pragma statement does accept an argument.

I suggest you edit your post title to something like "BUG: pragma_foreign_key_check() does not accept an argument." This will improve the likelihood that the bug is noted by SQLite's developer(s).

Minimal repro (mostly from OP):

create table foo(x integer primary key);
create table bar(y integer references foo(x));
select * from pragma_foreign_key_check(); -- showing table-valued function
select * from pragma_foreign_key_check('bar'); -- rejected for more than 0 args

This appears curable by editing the code in pragma.h reading:

#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
 {/* zName:     */ "foreign_key_check",
  /* ePragTyp:  */ PragTyp_FOREIGN_KEY_CHECK,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0,
  /* ColNames:  */ 37, 4,
  /* iArg:      */ 0 },
#endif

by applying s/PragFlg_Result0/PragFlg_Result1/ .

This is not a patch. I give away this suggestion freely, hereby put it into the public domain, and retain no rights to it.

(4) By Keith Medcalf (kmedcalf) on 2020-07-03 00:07:53 in reply to 2.3 [link] [source]

Actually, the pragma_foreign_key_check virtual table DOES NOT have hidden columns like some of the other pragma's that take schema and arg hidden columns (parameters).

sqlite> pragma table_xinfo('pragma_foreign_key_check');
┌─────┬────────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │  name  │ type │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼────────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │        │      │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ table  │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ rowid  │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ parent │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ fkid   │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴────────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_xinfo('pragma_foreign_key_list');
┌─────┬───────────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │   name    │ type │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼───────────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │           │      │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ id        │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ seq       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ table     │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ from      │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ to        │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ on_update │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ on_delete │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ match     │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ arg       │      │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
│ 9   │ schema    │      │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
└─────┴───────────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘

Rather, the pragma_foreign_key_check merely returns data, and if you want data for only one table then you use the WHERE clause to specify a constraint on the table column.

** Note that the results I obtain with table_xinfo may not match exactly what you get as I may have modified the pragma code to return additional rows and columns from the internal schema data structure **

Note that many pragma's are of the form
pragma <schema>.<pragma>(<arg>)
which then become the virtual table
`pragma_<pragma> with <schema> and <arg> as hidden columns.

This is not the case for pragma's and their virtual tables that are not in that form. In the particular case of pragma_foreign_key_check the table name is a column of the output, not a hidden column, so it must be specified in a WHERE clause and not as an argument to the virtual table.

(5.1) By Larry Brasfield (LarryBrasfield) on 2020-07-03 00:51:40 edited from 5.0 in reply to 4 [source]

I can see that this suggests a work-around, such as: select * from pragma_foreign_key_check() where "table"='bar'; for the OP.

However, according to the doc I linked, because the "pragma foreign_key_check" statement does accept an argument, optionally, the corresponding table-valued function should also. I see nothing contravening this in Pragma Functions.

Because there is a work-around per your point, I could see calling this a doc bug, fixed by explaining the exception(s) to the promise made there now. Or, more simply and with no increase in code volume, the flag change I suggest in my post #2 (as a later edit) would just make the code work as now documented.

(6) By Keith Medcalf (kmedcalf) on 2020-07-03 00:55:53 in reply to 5.0 [link] [source]

Presumably the purpose of foreign_key_check is to check foreign keys. In that light I would expect it to take either both a schema and arg (to allow selection of checking by either schema or table or both).

At the moment there is no way to specify that one wants to perform the check only on a specific table, or a specific schema, or a specific table in a specific schema.

Nor does it output what schema the table being checked is located in.

This makes its behaviour rather undefined in quite a few instances.

(7) By Larry Brasfield (LarryBrasfield) on 2020-07-03 01:26:24 in reply to 6 [link] [source]

Per the "Pragma functions" doc, the "feature is experimental and is subject to change. Further documentation will become available if and when the table-valued functions for PRAGMAs feature becomes officially supported." It might be helpful to state how the behavior is undefined.

To your specific points:

At the moment there is no way to specify that one wants to perform the check only on a specific table, or a specific schema, or a specific table in a specific schema.

and

Nor does it output what schema the table being checked is located in.

, the schema is implicit in the use of the table-valued function, which in many cases can be prefixed by schema. to specify it.

The pragma_foreign_key_check() function operates on only the schema named or defaulted. (main.pragma_foreign_key_check() by default) This seems well defined in the code and the doc for that pragma function. But not so in the doc for pragma_foreign_key_list(table-name). (This appears to be an oversight.)

(8) By Keith Medcalf (kmedcalf) on 2020-07-03 02:37:46 in reply to 7 [link] [source]

This is incorrect. Only the pragma format allows you to specify the schema (except in the case of foreign_key_check, which does not). For most of the pragma_<pragmaname> virtual table functions there is a hidden column called schema and a hidden column called arg which can be used to specify the schema and arg (from the syntax pragma schema.pragmaname(arg)).

The foreign_key_check pragma neither accepts nor reports the schema name.

And in ABSOLUTELY ZERO CASES can you preface the table-valued-function name with the schema and have that have any effect. All table-valued-functions exist in every schema and specifying the schema prefix is a no-op.

sqlite> select * from temp.pragma_table_xinfo('SysColumns');
┌─────┬─────────────────┬──────┬──────┬────────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │      name       │ type │ aff  │  coll  │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼─────────────────┼──────┼──────┼────────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0   │ ObjectSchema    │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ ObjectType      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ ObjectName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ ColumnID        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ ColumnName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ Type            │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ Affinity        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ Collation       │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ isNotNull       │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ DefaultValue    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ isPrimaryKey    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 11  │ isRowID         │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 12  │ isAutoIncrement │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 13  │ isHidden        │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴─────────────────┴──────┴──────┴────────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> select * from main.pragma_table_xinfo('SysColumns');
┌─────┬─────────────────┬──────┬──────┬────────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │      name       │ type │ aff  │  coll  │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼─────────────────┼──────┼──────┼────────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0   │ ObjectSchema    │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ ObjectType      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ ObjectName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ ColumnID        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ ColumnName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ Type            │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ Affinity        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ Collation       │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ isNotNull       │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ DefaultValue    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ isPrimaryKey    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 11  │ isRowID         │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 12  │ isAutoIncrement │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 13  │ isHidden        │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴─────────────────┴──────┴──────┴────────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> select * from _tz_.pragma_table_xinfo('SysColumns');
┌─────┬─────────────────┬──────┬──────┬────────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │      name       │ type │ aff  │  coll  │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼─────────────────┼──────┼──────┼────────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0   │ ObjectSchema    │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ ObjectType      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ ObjectName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ ColumnID        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ ColumnName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ Type            │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ Affinity        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ Collation       │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ isNotNull       │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ DefaultValue    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ isPrimaryKey    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 11  │ isRowID         │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 12  │ isAutoIncrement │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 13  │ isHidden        │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴─────────────────┴──────┴──────┴────────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> select * from pragma_table_xinfo('SysColumns') where schema='temp';
┌─────┬─────────────────┬──────┬──────┬────────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │      name       │ type │ aff  │  coll  │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼─────────────────┼──────┼──────┼────────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0   │ ObjectSchema    │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ ObjectType      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ ObjectName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ ColumnID        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ ColumnName      │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ Type            │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ Affinity        │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ Collation       │      │ BLOB │ NOCASE │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ isNotNull       │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ DefaultValue    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ isPrimaryKey    │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 11  │ isRowID         │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 12  │ isAutoIncrement │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 13  │ isHidden        │      │ BLOB │ BINARY │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴─────────────────┴──────┴──────┴────────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> select * from pragma_table_xinfo('SysColumns') where schema='_tz_';
sqlite> select * from pragma_table_xinfo('SysColumns') where schema='main';

(9) By Keith Medcalf (kmedcalf) on 2020-07-03 02:50:13 in reply to 7 [link] [source]

I just went and looked at the code. The code does not recognize a schema name at all. If no arguments are provided, it simply loops though all schema's, and then in each schema, though each table, then for each table through each foreign key constraint, and does its check, then outputs its results (with no attribution as to the schema wherein the checked foreign key constraint was found).

If you specify an arg in the pragma form, then that arg is used as the table name to search for in each schema, and results are output for all "found" tables with that name with no attribution of the particular schema.

When used in the virtual table format, no argument is permitted so all foreign keys on all tables in every schema are checked with no output of the schema name, and filtering may be applied to the candidates (however all fk in all tables in all schema's are still checked).

I'd consider this fine, but the schema name should be an output column, and perhaps the schema name and table name should be input constraints with the default being to check all constraints in all tables in all schemas.

(3) By Larry Brasfield (LarryBrasfield) on 2020-07-02 23:44:36 in reply to 1.1 [link] [source]

Incidentally, I would recommend this modified version of bar creation: create table bar(y integer references foo(x)); You will find that this modifies the pragma_foreign_key_list() output favorably, by making the foreign key reference explicit. It is a favor to readers of the SQL as well.

(10) By Richard Hipp (drh) on 2020-07-03 12:34:25 in reply to 1.1 [link] [source]

Please try again using the latest prerelease snapshot and report back success or failure. Thanks.

(11) By Jonas Rodrigues (jxonas) on 2020-07-03 14:04:44 in reply to 10 [link] [source]

Sucess.

Thanks Richard for the awesome work!