SQLite foreign Key

SQLite foreign KEY (Foreign key) support

FOREIGN KEY constraints are supported starting from SQLite 3.6.19. (The SQLite version of Ubuntu 10.04 is 3.6.22, and the SQLite version of Debian 6.0 is 3.7.0)

A FOREIGN KEY constraint is used to enforce a "presence" relationship between two tables. For example, consider the schema created by the following SQL command

CREATE TABLE Artist (
ArtistID INTEGER PRIMARY KEY,
Artistname TEXT
);
CREATE TABLE Track (
TrackID INTEGER,
TrackName TEXT,
Trackartist integer-must be mapped to a Artist.artistid record
);
Applications using this database can assume that for each row in the track table, there is a corresponding row in the artist. Unfortunately, if the user edits the database using an external tool, or there is a bug in the application. It is possible to insert a row in the track table without a corresponding record in the artist table.  Alternatively, deleting some rows in the artist table leaves orphan rows (orphaned rows) in the track table, and they find any corresponding rows in the remaining records in the artist tables. This may cause an error in the functionality of the app later. Or at least make the application more complex to write.

One workaround is to add a foreign key constraint to the database and enforce a constraint between the tables artist and track. The track table that adds a foreign key definition is defined as follows:
CREATE TABLE Track (
TrackID INTEGER,
TrackName TEXT,
Trackartist INTEGER,
FOREIGN KEY (trackartist) REFERENCES artist (ArtistID)
);

This allows the foreign key constraint to be enforced by SQLite. An attempt to insert a row into the track table that does not have corresponding data in the artist table is destined to fail, O (∩∩) o

If there are records in the track table that depend on a row in artist, then attempting to delete the row from the artist table will also fail.

In other words, for each row in the track table, the following expression is true:

Trackartist is NULL OR EXISTS (SELECT 1 from artist WHERE artistid=trackartist)

Enable foreign key support in SQLite

1) in order to use foreign key constraints in SQLite, when compiling the SQLite library, you cannot use ignore foreign keys and triggers, that is, Sqlite_omit_foreign_key and Sqlite_omit_trigger cannot be defined

2) must be open at run time because it is turned off by default

PRAGMA Foreign_keys = on;

database indexes for requirements and recommendations

In general, the parent key of a FOREIGN key constraint is the primary key in the parent table. If they are not primary keys, then the parent key bar must be subject to a unique constraint or have a unique index.

If the database schema also has foreign key errors, you need to look at multiple tables to find errors. These errors are not detected when the data table is created.

These errors prevent the application from using SQL statements to modify the contents of the child table or parent table. Report "DML Errors" when content is changed, and report "DDL errors" when schema is changed

In other words, a FOREIGN KEY constraint is incorrectly configured, requiring that a DML error be checked for the child table and parent table, typically "foreign key mismatch" or "no such table"

SELECT rowid from <child-table> WHERE <child-key> =:p arent_key_value
If the select returns data, SQLite determines that deleting a row from the parent table will violate the FOREIGN KEY constraint and return an error. A similar query is produced if the value of the parent key is modified or a new row is inserted in the parent table.

If these queries do not use indexes, they will force a linear lookup (scan) of the entire child table, which can be costly.

In most real systems, you should index the sub-key column. The index of the subkey does not have to be (and is not usually necessary) to have a unique index (because multiple rows in the child table correspond to a row in the parent table)

CREATE INDEX Trackindex on track (trackartist);

On DELETE and on update behavior

The on DELETE and on update clauses of the foreign key can be used to configure the behavior (on delete) that occurs when certain rows are deleted from the parent table. or modify the value of the existing row's parent key, the behavior that occurs (on UPDATE)

A single FOREIGN KEY constraint can configure different behavior for on delete and on update. Foreign key behavior is similar to triggers in many cases (trigger)

The behavior of on delete and on update is NO action, RESTRICT, set NULL, set DEFAULT, or CASCADE

If the star is not explicitly specified, then the default is no ACTION

No action: No special behavior occurs when the parent key is modified or deleted

RESTRICT: The application suppresses deletion (on delete RESTRICT) or modifies (on UPDATE RESTRICT) parent key when one or more child keys correspond to the corresponding parent key

The difference between restrict and a normal foreign key constraint is that when the field is updated, the Restrict behavior occurs immediately

SET null: Parent key is deleted (on delete set null) or modified (on UPDATE set NULL)

Set DEFAULT: Similar to set NULL

CASCADE: The delete or update operation that is implemented on the parent key is propagated to the child key associated with it.

For on delete CASCADE, every 1 rows in the child table associated with the rows in the deleted parent table are also deleted.

For on UPDATE CASCADE, every 1 rows stored in the child table, the corresponding field values are automatically modified to match the new parent key

Example:

CREATE TABLE Artist (

ArtistID INTEGER PRIMARY KEY,

Artistname TEXT

);

CREATE TABLE Track (

TrackID INTEGER,

TrackName TEXT,

Trackartist INTEGER REFERENCES Artist (ArtistID) on UPDATE CASCADE

);

INSERT into artist VALUES (1, ' Dean Martin ');

INSERT into artist VALUES (2, ' Frank Sinatra ');

INSERT into track VALUES (' Mr Bojangles ', 2);

INSERT into track VALUES (Amore, 2);

INSERT into track VALUES (' Christmas Blues ', 1);

INSERT into track VALUES ("My", 2);

sqlite> PRAGMA foreign_keys = on;

(Closed by default, to open at run time)

Sqlite> SELECT * from artist;

1| Dean Martin

2| Frank Sinatra

Sqlite> SELECT * from track;

14|mr. bojangles|2

15| That ' s amore|2

12| Christmas blues|1

13| My way|2

sqlite> UPDATE artist SET ArtistID = 999 WHERE artistname = ' Dean Martin ';

(Change the ArtistID column in the artist table for Dean Martin.)

In general, this creates a constraint, because one record in the track table becomes an orphan record

However, when the foreign key definition uses the ON UPDATE CASCADE clause, the update is passed to the child table, so that the foreign key constraint is not broken)

Sqlite> SELECT * from artist;

2| Frank Sinatra

999| Dean Martin

Sqlite> SELECT * from track;
14|mr. bojangles|2
15| That ' s amore|2
12| Christmas blues|999
13| My way|2
Configuring an on update or on delete behavior does not imply that foreign key constraints are not necessarily met.
For example, if the on delete SET default behavior is configured, if there is no row record in the parent table corresponding to the default value in the Child table column, deleting the parent key will break the foreign key if the dependent child key exists in the child table.
Example:
sqlite> PRAGMA foreign_keys = on;
CREATE TABLE Artist (
ArtistID INTEGER PRIMARY KEY,
Artistname TEXT
);
CREATE TABLE Track (
TrackID INTEGER,
TrackName TEXT,
Trackartist INTEGER default 0 REFERENCES artist (artistid) on DELETE SET DEFAULT
);
INSERT into artist VALUES (3, ' Sammy Davis Jr. ');
INSERT into track VALUES (' Mr Bojangles ', 3);
Sqlite> DELETE from artist WHERE artistname = ' Sammy Davis Jr. ';
Error:foreign key constraint failed
Deleting a row from the parent table causes the associated subkey in the child table to be set to an integer 0
However, this value does not correspond to any row of data in the schedule. So, the foreign key constraint is broken and the exception is thrown
Sqlite> INSERT into artist VALUES (0, ' Unknown artist ');
Adds a row to the parent table with a primary key of 0
This way, deleting a record doesn't break the foreign key constraint.
Sqlite> DELETE from artist WHERE artistname = ' Sammy Davis Jr. ';

Sqlite> SELECT * from artist;
0| Unknown Artist
Sqlite> SELECT * from track;
14|mr. bojangles|0
This write is similar to the SQLite trigger (triggers), the on DELETE SET default behavior, in effect, is similar to the following after DELETE trigger
CREATE TRIGGER On_delete_set_default after delete on artist BEGIN
UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;
END;
A row in the parent table of a foreign key constraint is deleted, or when the value stored in the parent key is modified: the logical order of time is:
1. Execute the Before Trigger program
2. Check local (non-foreign key) constraints
3. Update or delete rows in the parent table
4. Perform the required foreign key behavior
5. Execute AFTER Trigger program

An important difference between the on update foreign key behavior and the SQL trigger is that the on update behavior is performed only if the value of the parent key is modified and the value of the parent key is modified to be different from the original.
The on update behavior does not execute if the value modified by the Update SET statement is the same as the original

SQLite foreign Key