Read Only Add Foreign Key Django Insert
Summary: in this tutorial, you will learn how to utilize the SQLite foreign cardinal constraint to enforce the relationships between related tables.
SQLite foreign cardinal constraint support
SQLite has supported foreign key constraint since version 3.6.19. The SQLite library must also be compiled with neither SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER.
To check whether your current version of SQLite supports foreign key constraints or not, y'all use the following control.
PRAGMA foreign_keys;
Code language: SQL (Structured Query Language) ( sql ) The control returns an integer value: 1: enable, 0: disabled. If the command returns nothing, it means that your SQLite version doesn't support strange cardinal constraints.
If the SQLite library is compiled with foreign key constraint support, the application can apply the PRAGMA foreign_keys control to enable or disable foreign fundamental constraints at runtime.
To disable foreign key constraint:
PRAGMA foreign_keys = OFF;
Code language: SQL (Structured Query Language) ( sql ) To enable foreign key constraint:
PRAGMA foreign_keys = ON;
Lawmaking language: SQL (Structured Query Language) ( sql ) Introduction to the SQLite foreign primal constraints
Let's start with ii tables: suppliers and supplier_groups :
CREATE Table suppliers ( supplier_id integer Master Key, supplier_name text NOT Nix, group_id integer Not NULL ); CREATE Table supplier_groups ( group_id integer Master Fundamental, group_name text Non Zero );
Code language: SQL (Structured Query Linguistic communication) ( sql ) Bold that each supplier belongs to 1 and simply ane supplier group. And each supplier group may accept nil or many suppliers. The relationship between supplier_groups and suppliers tables is one-to-many. In other words, for each row in the suppliers table, there is a corresponding row in the supplier_groups table.
Currently, there is no way to foreclose y'all from adding a row to the suppliers table without a corresponding row in the supplier_groups table.
In addition, you lot may remove a row in the supplier_groups tabular array without deleting or updating the corresponding rows in the suppliers table. This may leave orphaned rows in the suppliers table.
To enforce the relationship betwixt rows in the suppliers and supplier_groups tabular array, you use the foreign key constraints.
To add the foreign key constraint to the suppliers tabular array, y'all change the definition of the CREATE TABLE statement higher up as follows:
Drop TABLE suppliers; CREATE TABLE suppliers ( supplier_id INTEGER PRIMARY Central, supplier_name TEXT Non NULL, group_id INTEGER Not NULL, FOREIGN KEY (group_id) REFERENCES supplier_groups (group_id) );
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
The supplier_groups table is chosen a parent table, which is the tabular array that a foreign key references. The suppliers table is known as a kid table, which is the table to which the strange central constraint applies.
The group_id column in the supplier_groups table is called the parent key, which is a cavalcade or a ready of columns in the parent table that the foreign key constraint references. Typically, the parent primal is the primary key of the parent table.
The group_id column in the suppliers table is called the child primal. More often than not, the child key references to the primary fundamental of the parent table.
SQLite foreign key constraint example
First, insert iii rows into the supplier_groups table.
INSERT INTO supplier_groups (group_name) VALUES ('Domestic'), ('Global'), ('One-Time');
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
Second, insert a new supplier into the suppliers tabular array with the supplier group that exists in the supplier_groups tabular array.
INSERT INTO suppliers (supplier_name, group_id) VALUES ('HP', 2);
Code linguistic communication: SQL (Structured Query Language) ( sql ) This statement works perfectly fine.
Tertiary, endeavor to insert a new supplier into the suppliers table with the supplier grouping that does not exist in the supplier_groups table.
INSERT INTO suppliers (supplier_name, group_id) VALUES('ABC Inc.', iv);
Code language: SQL (Structured Query Language) ( sql ) SQLite checked the foreign key constraint, rejected the change, and issued the following error message:
[SQLITE_CONSTRAINT] Abort due to constraint violation (Foreign KEY constraint failed)
Lawmaking language: CSS ( css ) SQLite strange fundamental constraint actions
What would happen if y'all delete a row in the supplier_groups table? Should all the corresponding rows in the suppliers table are too deleted? The aforementioned questions to the update performance.
To specify how foreign primal constraint behaves whenever the parent fundamental is deleted or updated, yous use the ON DELETE or ON UPDATE activeness as follows:
FOREIGN KEY (foreign_key_columns) REFERENCES parent_table(parent_key_columns) ON UPDATE action ON DELETE activeness;
Code linguistic communication: SQL (Structured Query Language) ( sql ) SQLite supports the post-obit actions:
- SET Nothing
- Ready DEFAULT
- RESTRICT
- NO ACTION
- CASCADE
In practice, the values of the master fundamental in the parent table exercise not modify therefore the update rules are less of import. The more important rule is the DELETE rule that specifies the action when the parent key is deleted.
We'll examine each action by the following example
SET NULL
When the parent key changes, delete or update, the corresponding child keys of all rows in the child table set up to NULL.
Offset, drop and create the table suppliers using the Prepare NULL action for the group_id foreign cardinal:
Drib TABLE suppliers; CREATE Tabular array suppliers ( supplier_id INTEGER Main Central, supplier_name TEXT Non NULL, group_id INTEGER, Foreign Key (group_id) REFERENCES supplier_groups (group_id) ON UPDATE Set NULL ON DELETE SET NULL );
Code language: SQL (Structured Query Language) ( sql ) Second, insert some rows into the suppliers table:
INSERT INTO suppliers (supplier_name, group_id) VALUES('XYZ Corp', 3); INSERT INTO suppliers (supplier_name, group_id) VALUES('ABC Corp', 3);
Code linguistic communication: SQL (Structured Query Language) ( sql ) 3rd, delete the supplier group id 3 from the supplier_groups table:
DELETE FROM supplier_groups WHERE group_id = three;
Lawmaking language: SQL (Structured Query Language) ( sql ) Fourth, query data from the suppliers table.
SELECT * FROM suppliers;
Code language: SQL (Structured Query Language) ( sql ) 
The values of the group_id column of the corresponding rows in the suppliers table set to NULL.
Gear up DEFAULT
The SET DEFAULT activeness sets the value of the foreign primal to the default value specified in the column definition when you lot create the table.
Considering the values in the column group_id defaults to NULL, if you delete a row from the supplier_groups table, the values of the group_id volition set to Goose egg.
Afterwards assigning the default value, the foreign central constraint kicks in and carries the check.
RESTRICT
The RESTRICT activeness does not allow you to change or delete values in the parent key of the parent table.
Offset, drib and create the suppliers table with the RESTRICT action in the strange cardinal group_id:
Drib Tabular array suppliers; CREATE TABLE suppliers ( supplier_id INTEGER Principal Primal, supplier_name TEXT Non Aught, group_id INTEGER, Foreign Fundamental (group_id) REFERENCES supplier_groups (group_id) ON UPDATE RESTRICT ON DELETE RESTRICT );
Code language: SQL (Structured Query Language) ( sql ) 2d, insert a row into the tabular array suppliers with the group_id 1.
INSERT INTO suppliers (supplier_name, group_id) VALUES('XYZ Corp', 1);
Code language: SQL (Structured Query Language) ( sql ) 3rd, delete the supplier grouping with id 1 from the supplier_groups tabular array:
DELETE FROM supplier_groups WHERE group_id = 1;
Code language: SQL (Structured Query Language) ( sql ) SQLite issued the following error:
[SQLITE_CONSTRAINT] Abort due to constraint violation (FOREIGN Primal constraint failed)
Code language: CSS ( css ) To set up it, you must first delete all rows from the suppliers table which has group_id i:
DELETE FROM suppliers WHERE group_id =i;
Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) Then, you lot can delete the supplier grouping 1 from the supplier_groups table:
DELETE FROM supplier_groups WHERE group_id = 1;
Lawmaking language: SQL (Structured Query Language) ( sql ) NO ACTION
The NO ACTION does not mean past-pass the foreign key constraint. It has the similar effect equally the RESTRICT.
Cascade
The CASCADE action propagates the changes from the parent table to the child table when you update or delete the parent fundamental.
Start, insert the supplier groups into the supplier_groups tabular array:
INSERT INTO supplier_groups (group_name) VALUES ('Domestic'), ('Global'), ('One-Fourth dimension');
Code language: SQL (Structured Query Language) ( sql ) Second, driblet and create the table suppliers with the CASCADE action in the foreign fundamental group_id :
DROP Tabular array suppliers; CREATE TABLE suppliers ( supplier_id INTEGER PRIMARY Central, supplier_name TEXT NOT Goose egg, group_id INTEGER, Foreign KEY (group_id) REFERENCES supplier_groups (group_id) ON UPDATE CASCADE ON DELETE Cascade );
Code language: SQL (Structured Query Linguistic communication) ( sql ) Third, insert some suppliers into the tabular array suppliers:
INSERT INTO suppliers (supplier_name, group_id) VALUES('XYZ Corp', ane); INSERT INTO suppliers (supplier_name, group_id) VALUES('ABC Corp', two);
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
Fourth, update group_id of the Domestic supplier group to 100:
UPDATE supplier_groups Set group_id = 100 WHERE group_name = 'Domestic';
Code linguistic communication: SQL (Structured Query Language) ( sql ) Fifth, query data from the tabular array suppliers:
SELECT * FROM suppliers;
Code language: SQL (Structured Query Language) ( sql ) 
As you lot tin see the value in the group_id column of the XYZ Corp in the tabular array suppliers changed from ane to 100 when we updated the group_id in the suplier_groups table. This is the result of ON UPDATE CASCADE action.
6th, delete supplier group id two from the supplier_groups table:
DELETE FROM supplier_groups WHERE group_id = 2;
Code language: SQL (Structured Query Linguistic communication) ( sql ) Seventh, query data from the table suppliers :
SELECT * FROM suppliers;
Lawmaking language: SQL (Structured Query Linguistic communication) ( sql ) 
The supplier id 2 whose group_id is ii was deleted when the supplier group id 2 was removed from the supplier_groups table. This is the effect of the ON DELETE Cascade action.
In this tutorial, you have learned about SQLite foreign cardinal constraint and how to use them to enforce the relationship betwixt related tables.
Was this tutorial helpful ?
Source: https://www.sqlitetutorial.net/sqlite-foreign-key/
0 Response to "Read Only Add Foreign Key Django Insert"
Post a Comment