SQL Database with Check constrain

So I working on a small project where I need to keep data in a database.  In this case sqlite3, mainly because it is fast and reliable.  And I don´t need the functionality of my most use mysql.

So I need to have a database that holds nodes (clients), a role list (a slqserver or webserver e.g) and a whitelisting table keeping track of the nodes that I whitelist on my site.

The role and whitelisting table are straight forward nothing new there.

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));

My thought was to make sure that only a MAC looking input was inserted into my database. Hmm – okay I could choose to set “mac char(17) and maximize the input length. But will this help me making sure that a mac like input is inserted with the sql query – no.  In my reading I fell over this CHECK constrain. So I added to my table Creation the following CHECK (mac LIKE “%%:%%:%%:%%:%%:%%” ) . Making it possible to control the insertion input in a manor that is acceptable for me.

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));

I tested this afterward with insert query

sqlite> Insert into nodesRole (node, mac, role, status) values (“TEST1”, “AA:BB:CC:DD:EE:FF”, ‘TESTROLE’, ‘DOWN’);
sqlite> select * from nodesRole;
TEST1|AA:BB:CC:DD:EE:FF|TESTROLE|DOWN

A good and expected result! So I Thought!

Sqlite3 ignores the length definitions in the char define so char(17) is equal to char (8000) that is the maximum of characters a char can take.

So  a control length in the check can fix this and actually makes it much better because I now can define that is must have 17 chars, no more nor less. I removed all the length definitions in the Create tables and this is my result.

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));

First test:
Can I insert a query that has too many chars in it:
sqlite> Insert into nodesRole (node, mac, role, status) values (“TEST2”, “AAA:BBA:CAC:DD:EE:FF”, ‘TESTROLE’, ‘DOWN’);
Error: constraint failed
NO

Second test:
Can I insert a query that has too few chars?
sqlite> Insert into nodesRole (node, mac, role, status) values (“TEST2”, “A:B:C:DD:EE:FF”, ‘TESTROLE’, ‘DOWN’);
Error: constraint failed
NO

Third Test:
Insert a query that meets the length requeriments?
sqlite> Insert into nodesRole (node, mac, role, status) values (“TEST2”, “AA:BB:CC:DD:EE:FF”, ‘TESTROLE’, ‘DOWN’);
YES
sqlite> SELECT * FROM nodesRole;
TEST2|AA:BB:CC:DD:EE:FF|TESTROLE|DOWN

And a selection query to verify