CODEWITHSUNDEEP
sqlite
user1032531
user1032531

Reputation: 26281

What does SQLite's MATCH NONE mean?

The following CREATE TABLE statement was exported from DB Browser for SQLite.

What does MATCH NONE do? (located in the last line in the CONSTRAINT statement)

Is it considered appropriate to explicitly define it in the CREATE TABLE statement?

CREATE TABLE IF NOT EXISTS `Reports` (
    `ID`    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    `ParameterTypeID`   integer,
    `ReportTypeID`  integer NOT NULL,
    `DurationToSpan`    integer NOT NULL,--# of Days or # of Week
    `SpanDurationType`  char ( 4 ) NOT NULL,--Day,Week
    `Name`  nvarchar ( 250 ) NOT NULL,
    `Description`   nvarchar ( 500 ) DEFAULT NULL,
    `DateCreated`   datetime DEFAULT current_timestamp,
    `DateModified`  datetime DEFAULT current_timestamp,
    `CreatedBy` nvarchar ( 100 ) DEFAULT 'Admin',
    `IsActive`  boolean DEFAULT 1,
    `SortBy`    char ( 4 ) NOT NULL, --Amit, confirm NOT NULL is okay with your code
    CONSTRAINT `FK_Reports_0_0` FOREIGN KEY(`ReportTypeID`) REFERENCES `ReportsType`(`ID`) MATCH NONE ON UPDATE NO ACTION ON DELETE NO ACTION
);

Upvotes: 1

Views: 386

Answers (3)

user1032531
user1032531

Reputation: 26281

"MATCH NONE" means that if any of the key columns in the child table are NULL, then there is no requirement for a corresponding row in the parent table.

Reference https://www3.sqlite.org/cgi/src/artifact/cf68fddd4643bbe3

Upvotes: 0

MikeT
MikeT

Reputation: 56938

What does MATCH NONE do?

In short nothing within SQLite when coded as a constraint in a Foreign Key.

Within a foreign key it has no impact on the foreign key processing, it is ignored but is still parsed and expects a keyword to follow it, which is also taken into account.

It basically supports the coding of the MATCH keyword and it's options SIMPLE, PARTIAL and FULL (and apparently ). However the second keyword is not limited to the the keywords SIMPLE, PARTIAL and FULL.

  • I believe that any word (unsure of limitations, if any, but as a test RUMPLESTILTSKIN as the second word was parsed) word is required to suit the 2 word format of the keyword(sic).

  • Note numeric's will result in a syntax error.

It basically caters for SQL from other DBM's to be used, as SQLite so often does.

MATCH NONE could have been coded to force an error in SQL if used by DM's, to highlight the need to consider the fact that SQLite foreign key processing could be different or need to be considered.

SQLite Foreign Key Support - 6. Limits and Unsupported Features

MATCH does have other uses in SQL outside of the foreign key :-

MATCH is a FTS3/4 (Full Text Search) operator, similar to LIKE but finds words e.g. MATCH('Fred') would find Fred was here but not Frederick was here.

SQLite FTS3 and FTS4 Extensions

MATCH is also an operator within expression as per :-

The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

SQL As Understood By SQLite - expression - The LIKE, GLOB, REGEXP, and MATCH operators

Is it considered appropriate to explicitly define it in the CREATE TABLE statement?

Probably yes, as it then highlights the difference, by way of a syntax error (possibly), that SQLite foreign key processing may/does differ.

Upvotes: 0

Shawn
Shawn

Reputation: 52374

It is unrelated to a MATCH expression in a WHERE clause despite sharing a keyword.

From the documentation on foreign keys:

According to SQL92, a MATCH clause may be attached to a composite foreign key definition to modify the way NULL values that occur in child keys are handled. If "MATCH SIMPLE" is specified, then a child key is not required to correspond to any row of the parent table if one or more of the child key values are NULL. If "MATCH FULL" is specified, then if any of the child key values is NULL, no corresponding row in the parent table is required, but all child key values must be NULL. Finally, if the foreign key constraint is declared as "MATCH PARTIAL" and one of the child key values is NULL, there must exist at least one row in the parent table for which the non-NULL child key values match the parent key values.

SQLite parses MATCH clauses (i.e. does not report a syntax error if you specify one), but does not enforce them. All foreign key constraints in SQLite are handled as if MATCH SIMPLE were specified.

I don't know what MATCH NONE is supposed to mean but it's another no-op like the other cases.

Stuff like that, the non-sqlite column types, and all the backticks around column names make me think this table was originally used with a different RDBMS and someone just copy and pasted the definition into sqlite, which, being very lenient about such things, actually accepted it.

Upvotes: 1

Related Questions