ON DELETE CASCADE appended after the FOREIGN KEY definition will cause records in the Child table to be deleted when a matching parent id is deleted. Sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,154,40.5) Īssuming that foreign key support has been enabled for the session.įoreign key constraints will prevent you from changing the key column in the parent table or deleting records in the parent table which have related records in the child tableĪppending ON DELETE CASCADE and ON UPDATE CASCADE will cause the SQLite Database Engine to make the necessary changes to the child table automatically. Sqlite> DELETE FROM timecard WHERE rowid = last_insert_rowid() įortunately the error is spotted immediately and corrected by deleting the recordīy using the last_insert_rowid() function as criteria.įoreign Keys is now enabled but the wrong value is still entered again.Ī value for EmpIDchild is entered which corresponds to a record in the employee table. Sqlite> SELECT * FROM timecard WHERE rowid = last_insert_rowid() Sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,150,40.5) The database engine happily accepts this orphan record, which may result in somebody not being paid for that week since their hours worked can't be related to their employee id. In the following example, foreign key support has not been enabled for the database session.Ī record is entered in the timecard table having an employee id number which is not found in the employee table. Since version 3.6.19, SQLite has included the capability to enforce Foreign Key constraints but this functionality currently must be activated for each database session by entering However the related column in the parent table must have a UNIQUE constraint otherwise a Cartesian Product will likely be the result. While most often related to the primary key of the parent table, it doesn't necessarily have to be that way. Sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,153,34.5) Ī foreign key constraint specifies that for each record in the table there must be a unique record that matches the key in the linked table Now reinsert the record with the corrected pay period value of 2. Sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,153,34.5) Įrror: columns PayPeriod, EmpIDchild are not unique The next record to be added has a combination of pay period and Employee id number that duplicates a record that has been previously entered. Sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,154,41.25) Sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,153,38.5) Sqlite> CREATE TABLE timecard(PayPeriod INTEGER,Hours REAL,EmpIDchild INTEGER,PRIMARY KEY (PayPeriod,EmpIDchild), FOREIGN KEY(EmpIDchild) REFERENCES employee(EmpIDparent)) In this particular instance there will be more than one record with the same employee id and certainly more than one record with the same week numberīut there should only be one record with the same combination of employee id and pay period number. The "timecard" table has what is commonly known as a Composite key, which is a primary key that uses two or more fields to uniquely identify each row in a table. Sqlite>INSERT INTO employee (EmpIDparent,FirstName,LastName,StartDate,EndDate,PayGrade,PayRate) VALUES(154,'Alan','Jones','',NULL,'元',17.79) Sqlite>INSERT INTO employee (EmpIDparent,FirstName,LastName,StartDate,EndDate,PayGrade,PayRate) VALUES(153,'Melvin','Roberts','',NULL,'元',18.19) ),EndDate CHAR(10),PayGrade CHAR(2), PayRate Real) Sqlite>CREATE TABLE employee(EmpIDparent INTEGER PRIMARY KEY,FirstName TEXT,LastName TEXT,StartDate CHAR(10 The "employee" table which lists employees and timecard which records the hours worked each week by each employee. In the following example, two tables are used to demonstrate primary and foreign key constraints mode columns" at the prompt as shown below. TIP: You can change to the Column mode, which is often easier to read by entering ". List mode, shown above is the default method of display in SQLite for a query result.Įach field in a record is separated by a delimiter, most often a pipe " | " character. Sqlite> SELECT rowid,color FROM colorlist In SQLite every table has a default PRIMARY KEY field called the "rowid" which is an integer that identifies the record within the table. Sqlite> INSERT INTO colorlist VALUES('blue') Sqlite> INSERT INTO colorlist VALUES('yellow') Sqlite> INSERT INTO colorlist VALUES('red') Sqlite> CREATE TABLE colorlist (color TEXT) Note that in the following table definition that no primary key field has been specified and that it is just a single column list of colors It is generally a good practice declare a primary key field in a CREATE TABLE statement. Relating Records Between Tables PRIMARY KEYĪ primary key is a field or combination of fields that uniquely identifies each record in a table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |