Relating Records Between Tables

PRIMARY KEY

A primary key is a field or combination of fields that uniquely identifies each record in a table. It is generally a good practice declare a primary key field in a CREATE TABLE statement. 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

sqlite> CREATE TABLE colorlist (color TEXT);
sqlite> INSERT INTO colorlist VALUES('red');
sqlite> INSERT INTO colorlist VALUES('yellow');
sqlite> INSERT INTO colorlist VALUES('blue');

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>.headers on
sqlite> SELECT rowid,color FROM colorlist;
rowid|color
1|red
2|yellow
3|blue
sqlite>

List mode, shown above is the default method of display in SQLite for a query result. Each field in a record is separated by a delimiter, most often a pipe " | " character.
TIP: You can change to the Column mode, which is often easier to read by entering ".mode columns" at the prompt as shown below.

sqlite> .mode columns
sqlite> SELECT rowid,color FROM colorlist;

rowid	color
----------	----------
1	red
2	yellow
3	blue

sqlite>

Constraints

In the following example, two tables are used to demonstrate primary and foreign key constraints within SQLite. The "employee" table which lists employees and timecard which records the hours worked each week by each employee.

sqlite>CREATE TABLE employee(EmpIDparent INTEGER PRIMARY KEY,FirstName TEXT,LastName TEXT,StartDate CHAR(10 ),EndDate CHAR(10),PayGrade CHAR(2) , PayRate Real);
sqlite>INSERT INTO employee (EmpIDparent,FirstName,LastName,StartDate,EndDate,PayGrade,PayRate) VALUES(153,'Melvin','Roberts','2009-09-19',NULL,'L3',18.19);
sqlite>INSERT INTO employee (EmpIDparent,FirstName,LastName,StartDate,EndDate,PayGrade,PayRate) VALUES(154,'Alan','Jones','2009-09-11',NULL,'L3',17.79);

sqlite> .mode columns
sqlite> .headers on
sqlite> SELECT * FROM employee;

EmpIDparent	FirstName	LastName	StartDate	EndDate	PayGrade	PayRate
-----------	----------	----------	----------	----------	----------	----------
153	Melvin	Roberts	40075		L3	18.19
154	Alan	Jones	40067		L3	17.79

sqlite>

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. 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 but there should only be one record with the same combination of employee id and pay period number.

sqlite> CREATE TABLE timecard(PayPeriod INTEGER,Hours REAL,EmpIDchild INTEGER,PRIMARY KEY (PayPeriod,EmpIDchild), FOREIGN KEY(EmpIDchild) REFERENCES employee(EmpIDparent));
sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,153,38.5);
sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,154,41.25);

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,153,34.5);
Error: columns PayPeriod, EmpIDchild are not unique

Now reinsert the record with the corrected pay period value of 2.

sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,153,34.5);
sqlite> SELECT * FROM timecard;

PayPeriod	Hours	EmpIDchild
----------	----------	----------
1	38.5	153
2	34.5	153
1	41.25	154

Foreign Keys

A 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 While most often related to the primary key of the parent table, it doesn't necessarily have to be that way. However the related column in the parent table must have a UNIQUE constraint otherwise a Cartesian Product will likely be the result. See Page 5 for more on this
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 PRAGMA foreign_keys = ON;.

In the following example, foreign key support has not been enabled for the database session. A record is entered in the timecard table having an employee id number which is not found in the employee table. 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.

sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,150,40.5);
sqlite> SELECT * FROM timecard WHERE rowid = last_insert_rowid();

PayPeriod	Hours	EmpIDchild
----------	----------	----------
2	40.5	150

sqlite> DELETE FROM timecard WHERE rowid = last_insert_rowid();

Fortunately the error is spotted immediately and corrected by deleting the record by using the last_insert_rowid() function as criteria.

Foreign Keys is now enabled but the wrong value is still entered again.

sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,150,40.5);
Error: foreign key constraint failed

A value for EmpIDchild is entered which corresponds to a record in the employee table.

sqlite> INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(2,154,40.5);
sqlite> SELECT * FROM timecard;

PayPeriod	Hours	EmpIDchild
----------	----------	----------
1	38.5	153
2	34.5	153
1	41.25	154
2	40.5	154

sqlite>

Cascade

Assuming that foreign key support has been enabled for the session.
Foreign 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 without first updating the child table. Appending ON DELETE CASCADE and ON UPDATE CASCADE will cause the SQLite Database Engine to make the necessary changes to the child table automatically.

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.
When ON UPDATE CASCADE is used, the foreign key field in the child table will be updated to match the record(s) in the parent table.

CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY, field_2 TEXT, foreign_key_field INTEGER , FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name(parent_key_field) ON DELETE CASCADE ON UPDATE CASCADE );

CHECK Constraints

A check constraint defines what is a valid value for a column.

field_name_1 REAL NOT NULL CHECK(field_name_1 >= 0)
field_name_2 TEXT NOT NULL CHECK(field_name_2 NOT IN ("string_1","string_2","string_3")

Working with SQLite TABLES

Add a column to an Existing Table

Sqlite will allow you to add a column or columns to an existing table using the "ALTER TABLE" statement, however the removal of a column from a table requires that the table be recreated and the data from the old table to be loaded into the revised table using a select query. See the section on making a copy of an existing table.

ALTER TABLE table_name ADD new_column_name data_type ;

sqlite> .schema inventorystat
CREATE TABLE inventorystat (naicscode INTEGER,year INTEGER,month INTEGER,inventoryval DOUBLE);
sqlite>ALTER TABLE inventorystat ADD descrip VARCHAR(50);
sqlite> .schema inventorystat
CREATE TABLE inventorystat(naicscode INTEGER,year INTEGER,month INTEGER,inventoryval DOUBLE,descrip VARCHAR(50));
sqlite>

In the example above we have entered ".schema inventorystat" to show the create statement for the table. The ALTER TABLE statement then adds the column "descrip" to the table specification which can be shown by entering ".schema inventorystat" once again.

Rename a Table

ALTER TABLE old_table_name RENAME TO new_table_name ;

sqlite> .tables

ReqDetail	ReqTotal	inventory	test1
ReqEquip	RequisitionDetail	reqdescrip	test2

sqlite> ALTER TABLE test2 RENAME TO backuplist;
sqlite> .tables

ReqDetail	ReqTotal	backuplist	reqdescrip
ReqEquip	RequisitionDetail	inventory	test1

sqlite>

In this example the table names "test2" is being renamed to "backuplist". The ".tables" statements show the list of the tables in the database before and after the change.

Deleting a table

Be very careful about using the "DROP TABLE" command. Once a table is gone, it is gone forever along with whatever data that it may have contained.

DROP TABLE table_name ;
DROP TABLE database_name.table_name ;

sqlite> .tables
CustomerBackup Customers asforum catalogsales
sqlite> DROP TABLE CustomerBackup;
sqlite> .tables
Customers asforum catalogsales
sqlite>

Renaming or Dropping Columns in Table

Sqlite has only limited ALTER TABLE support. Operations involving dropping columns, renaming columns or a combination of the two require that a new table be created with the changes incorporated in it. The original table is then dropped or renamed and the new table is renamed to the original table name. One thing to be aware of is that once a table is dropped, any associated triggers will be lost. It is a good idea to run a query against the sqlite_master table for the table that you are working on and copy them into a text editor so that they can be easily be reentered by copying and pasting the CREATE TRIGGER statement(s) into the command line. Triggers may have to be edited if any column names referenced in the trigger(s) have been changed

SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name';

sqlite> select sql from sqlite_master where tbl_name = 'ReqEquip';
sql
CREATE TABLE 'ReqEquip'(ReqNumber INTEGER PRIMARY KEY,Requestor VARCHAR(30) NOT NULL,Auth VARCHAR(30) NOT NULL,ReqDate CHAR(10) NOT NULL)
CREATE TRIGGER ReqNumDel
BEFORE DELETE ON 'ReqEquip'
FOR EACH ROW BEGIN
DELETE from ReqDetail WHERE ReqDetail.ReqNumber = OLD.ReqNumber;
END sqlite>

Rename Columns in a Table

Compose a CREATE TABLE statement with a new table name that uses an "AS SELECT" clause followed by a comma separated list of the columns names in the table.
For those column names that you wish to change, follow the name with single space and then the new name enclosed in single quotes.
Run the sql statement and confirm that the new table is structured as you want it and that the data from the old table has be loaded into the new table correctly.
Rename or drop the original table and rename the new table to the name of the original table.

CREATE TABLE temp_table_name AS SELECT old_field_name ' new_field_name', list_other_fields FROM table_name;
ALTER TABLE table_name RENAME TO archive_old_table;
ALTER TABLE temp_table_name RENAME TO table_name;

Drop columns from a Table

Compose a CREATE TABLE statement with a new table name that uses a SELECT AS clause followed by a list of the columns or field names that you wish to retain.
Run the sql statement and confirm that the new table is structured as you want it.
Drop the original table or rename it and rename the new table to the name of the original table.

Making a copy of an existing table within the main database

From time to time you may want to duplicate an existing table in a database to test table changes without risking the original data or to make a snapshot of the data for backup purposes. Additionally, if you wish to change column names or remove one or more columns from a sqlite table then it will be necessary to create a table with the desired changes and to copy the data using a sql query before renaming the original and the new tables.

CREATE TABLE new_table_name AS SELECT * FROM original_table_name;

sqlite> CREATE TABLE CustomersCopy AS SELECT * FROM Customers;
sqlite> SELECT * FROM CustomersCopy;
130169|Acme Widgets|1744 Alder Road|Apt 31C|Springfield|VA|20171|Alan Allen|57551267
130208|Nike Missiles Inc|5946 Oak Drive||Springfield|VA|20171|Lucy Baker|57155762
130247|Charlies Bakery|7116 Ginko St|suite 100|Springfield|VA|20171|Susan Nordrom|5715552363
130286|Unisales Inc.|8438 Maple Ave||Springfield|VA|20171-3521|Roger Norton|57551418
130325|M.I. Sinform & Sons|1785 Elm Avenue|P.O. Box 31|Springfield|VA|20171|Mi I. Sinform|5715558760
130364|Big Dents Towing Inc.|7578 Spruce St.|Building 31 A|Springfield|VA|20175231|George Spencer|5715557855
130365|Weneverpay Inc|428 Holly Ct||Springfield|VA|20171|Peter Norton|57155543
sqlite>

If you wish only to copy the table structure without copying the records then add a limit of 0 to the end of the statement.

CREATE TABLE new_table_name AS SELECT * FROM original_table_name LIMIT 0;

sqlite> CREATE TABLE CustomersEmpty AS SELECT * FROM Customers LIMIT 0;
sqlite> Select * from CustomersEmpty;
sqlite>
sqlite> .schema CustomersEmpty
CREATE TABLE CustomersEmpty(
AcctNumber INTEGER,
Custname VARCHAR(50),
Addr1 VARCHAR(50),
Addr2 VARCHAR(50),
City VARCHAR(30),
State CHAR(2),
Zipcode VARCHAR(10),
Contact VARCHAR(30),
Phone VARCHAR(10)
);

Colin Riley -- February 2011 --