The ON DELETE actions are therefore more interesting practically speaking than the ON UPDATE actions. ON UPDATEĪn update to a parent record should not change the primary key.
There are other actions that can be taken however.
The default action for a foreign key constraint violation is to prevent the action causing the violation. In this case the parent record is optional. A foreign key could have a hull value if it is not required to be associated to a parent record. The foreign keys in my example are not nullable, but this does not have to be the case. Table Constraint Example CREATE TABLE IF NOT EXISTS orders (Īpplying foreign keys in this manner allows for flexibility allowing for declaration of a foreign key on a multi-column primary key. Product_id INTEGER NOT NULL REFERENCES products (product_id), Inline Example CREATE TABLE IF NOT EXISTS orders (Ĭustomer_id INTEGER NOT NULL REFERENCES customers (customer_id), The foreign key can be declared with the column definition by adding a references statement or by using a foreign key declaration after all column definitions. If your application is dependent on this setting, it should explicitly turn it on or off.” (Kreibich) Declaring Foreign Key Constraints “A future version of SQLite may have foreign key constraints enabled by default. Use the following statement to enable foreign key enforcement PRAGMA foreign_keys = ON įoreign keys are not enforced by default in order to avoid causing problems with databases created prior to foreign key support. The Foreign Keys enforce the rule that an order must be associated with an existing customer and an existing product. The child table has a foreign key constraint on each of the parent tables.
You can see there are 2 parent tables and one child table. The SQL command examples used in this article are based on the following database. If the column names are incorrect the foreign key will be created but will not function.Do not add commas at the end of foreign key declarations.If the column is not specified, the foreign key will use the primary key.Foreign keys can be declared after all column declarations, or inline.Foreign key enforcement need to be enabled on the database (Using a Pragma).SQLite provides excellent support for foreign keys and has some limitations. Some important points when creating foreign keys in SQLite are: “The vast majority of foreign keys will reference a primary key, but if a column other than the primary key is used, that column must have a UNIQUE constraint, or it must have a single-column UNIQUE index.” (Kreibich) The child table records the parent table’s primary key and is known as a reference to the parent table. SQLite provides excellent support for foreign keys but has some inherited limitations.Ī Parent-Child relationship connects 2 tables, the parent table must have a primary key and the child table defines the foreign key. It is not uncommon to run SQL directly against a database, and if business rules can be enforced in this scenario, your application data is better protected from inconsistency. Parent-child relationships can be supported without foreign keys and enforced programmatically in the application layer, but database enforced relationship constraints provide an extra level of protection. Foreign keys ensure there is referential integrity in the row data and that there aren’t orphaned child records. Foreign Keys, or more properly, Foreign Key Constraints are used to enforce parent-child relationships between tables.
0 kommentar(er)
