database_creation.sql

-- rim of database
CREATE TABLE owner (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 name VARCHAR(255) NOT NULL
);

CREATE TABLE device (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 name VARCHAR(255) NOT NULL,
 owner_id INTEGER NOT NULL,
 FOREIGN KEY (owner_id) REFERENCES owner (id)
 ON UPDATE CASCADE
 ON DELETE CASCADE
);

CREATE TABLE key (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 license_key VARCHAR(255) NOT NULL,
 in_use BOOLEAN NOT NULL DEFAULT 0,
 device_id INTEGER DEFAULT NULL,
 FOREIGN KEY (device_id) REFERENCES device (id)
 ON UPDATE CASCADE
 ON DELETE SET DEFAULT
);

CREATE UNIQUE INDEX owner_name ON owner (name);
CREATE UNIQUE INDEX device_name ON device (name);
CREATE UNIQUE INDEX license_key_id ON key (license_key);