SQL Database with Check constrain

SQLite3’s CHECK constraint lets you enforce input format rules at the database level, catching bad data before it lands in a table.

Overview

This project stores network nodes in an SQLite3 database. The schema tracks nodes (clients), roles (such as sqlserver or webserver), and a whitelist table. The role and whitelist tables are straightforward:

CREATE TABLE role (
  role CHAR(255) NOT NULL PRIMARY KEY
);

CREATE TABLE whitelist (
  node CHAR(255) NOT NULL UNIQUE,
  status CHAR(20) DEFAULT "Not Whitelisted",
  FOREIGN KEY (node) REFERENCES nodeRole(node)
);

The nodesRole table also needs to store a MAC address. Setting mac CHAR(17) caps the column length in most databases, but SQLite3 ignores length definitions on CHAR. CHAR(17) behaves identically to CHAR(8000) in SQLite3, so the length constraint provides no protection.

A CHECK constraint solves this. Adding CHECK (mac LIKE "%%:%%:%%:%%:%%:%%") forces the value to match a colon-separated pattern:

CREATE TABLE nodesRole (
  node CHAR(255) NOT NULL PRIMARY KEY,
  mac  CHAR(17)  NOT NULL UNIQUE CHECK (mac LIKE "%%:%%:%%:%%:%%:%%"),
  role CHAR(255) NOT NULL,
  status CHAR(10) NOT NULL DEFAULT "DOWN",
  FOREIGN KEY (role) REFERENCES role(role)
);

A first test showed this works, but the CHAR(17) length definition still does nothing in SQLite3. Combining the LIKE pattern with LENGTH(mac) = 17 closes that gap and enforces exactly 17 characters. Removing the length definitions from all columns gives the final schema:

CREATE TABLE nodesRole (
  node   CHAR NOT NULL PRIMARY KEY,
  mac    CHAR NOT NULL UNIQUE CHECK (mac LIKE "%%:%%:%%:%%:%%:%%" AND LENGTH(mac) = 17),
  role   CHAR NOT NULL,
  status CHAR NOT NULL DEFAULT "DOWN",
  FOREIGN KEY (role) REFERENCES role(role)
);

Validation tests

Test 1: Insert a MAC address with too many characters:

sqlite> INSERT INTO nodesRole (node, mac, role, status)
   ...> VALUES ("TEST2", "AAA:BBA:CAC:DD:EE:FF", 'TESTROLE', 'DOWN');
Error: constraint failed

Rejected.

Test 2: Insert a MAC address with too few characters:

sqlite> INSERT INTO nodesRole (node, mac, role, status)
   ...> VALUES ("TEST2", "A:B:C:DD:EE:FF", 'TESTROLE', 'DOWN');
Error: constraint failed

Rejected.

Test 3: Insert a valid MAC address:

sqlite> INSERT INTO nodesRole (node, mac, role, status)
   ...> VALUES ("TEST2", "AA:BB:CC:DD:EE:FF", 'TESTROLE', 'DOWN');

sqlite> SELECT * FROM nodesRole;
TEST2|AA:BB:CC:DD:EE:FF|TESTROLE|DOWN

Accepted. The CHECK constraint with LENGTH enforces both format and exact length.