A foreign key is a schema constraint requiring values in one table to reference corresponding values in another table. It protects data integrity by linking a referencing column to a key in the referenced table. Constraints can slow large data loads because indexes must be updated and rules checked. section 3.3.1
Link readings to systems
Define the referenced table first, then constrain the reading table: CREATE TABLE IF NOT EXISTS systems ( id INTEGER PRIMARY KEY, name VARCHAR(128) NOT NULL ); CREATE TABLE IF NOT EXISTS readings ( system_id INTEGER NOT NULL, read_on TIMESTAMP NOT NULL, power DECIMAL(10,3) NOT NULL DEFAULT 0 CHECK(power >= 0), PRIMARY KEY (system_id, read_on), FOREIGN KEY (system_id) REFERENCES systems(id) ); The composite primary key identifies each system reading by system and timestamp. Later, a query can join readings to systems by matching system_id with id; the foreign key is not required for the join itself. section 3.3.1 section 3.4.4
Listing 3.2 Creating the readings table with an idempotent statement
CREATE TABLE IF NOT EXISTS readings (
system_id INTEGER NOT NULL,
read_on TIMESTAMP NOT NULL,
power DECIMAL(10,3) NOT NULL
DEFAULT 0 CHECK(power >= 0), #1
PRIMARY KEY (system_id, read_on), #2
FOREIGN KEY (system_id)
REFERENCES systems(id) #3
);
Inspect the complete CREATE TABLE definition for a readings table using a foreign key, composite primary key, and data-quality constraint. section 3.3.1
