SQLite3: FOREIGN KEY constraint

1. Before using a FOREIGN key constraint, it must be determined that sqlite3 is the appropriate compiled version, original: In order to use foreign key constraints in SQLite, the library must is compiled with Neith Er sqlite_omit_foreign_key or Sqlite_omit_trigger defined.

2. FOREIGN KEY switch

You must use pragma to open the foreign key switch to support foreign key constraints.

sqlite> PRAGMA foreign_keys;0sqlite> PRAGMA foreign_keys = on;sqlite> PRAGMA foreign_keys;1sqlite> PRAGMA Foreign_keys = off;sqlite> PRAGMA foreign_keys;0

3. Foreign KEY statement (from Sqlite3 document):

4. Terminology

The parent table, child table, parent key, and child keys are used in SQLite to describe foreign key constraints.

5. Binding Nature

The foreign key of the child table's record must be empty or present in the parent table, if it must be the parent table's primary key or a field with a unique option.

That is to say, the constant is true:

Child_table_foreign_key is NULL OR EXISTS (SELECT primary_key_or_unique_col from parent_table WHERE parimary_key_or_ Unique_col=chidl_table_foreign_key)

When the child table record for the parent key is still present, the record of the parent key is not allowed to be deleted by default. You can use the on delete or the ON Update option to specify an action.

You can use triggers when you must delete records in a child table that corresponds to a parent key (cascade delete).

6. Create a table with a FOREIGN KEY constraint

CREATE TABLE artist (ArtistID INTEGER PRIMARY KEY, Artistname TEXT); CREATE TABLE Track (TrackID integer, TrackName TEXT, trackartist Integer, FOREIGN KEY (trackartist) REFERENCES artist (Artis TID));

Or

CREATE TABLE Track (TrackID integer, TrackName TEXT, trackartist integer REFERENCES artist (ArtistID));

Note: When you use the second notation, seemingly MySQL5.0 foreign key constraints do not work. Therefore, it is recommended that the first one be written, and preferably preceded by a constraint name constraint symbol;

7.ON DELETE and on UPDATE Actions

Not finished, participate in

Http://www.sqlite.org/foreignkeys.html#fk_actions

SQLite3: FOREIGN KEY constraint