I'm trying to learn to use sqlite3 and I have encountered an oddity. I
don't have another system to test on at the moment. So I'm not sure if
its me, sqlite3, or netbsd. Either way. Here are the commands give:
laptop$ /usr/bin/sqlite3 test.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> CREATE TABLE os_groups (
...> os_group INTEGER PRIMARY KEY,
...> os_type TEXT NOT NULL
...> );
sqlite> INSERT INTO os_groups (os_type)
...> VALUES
...> ('BSD'),
...> ('LINUX'),
...> ('other');
sqlite> CREATE TABLE os (
...> os_id INTEGER PRIMARY KEY,
...> os_name TEXT NOT NULL,
...> os_group INTEGER,
...> FOREIGN KEY (os_group)
...> REFERENCES os_groups (os_type)
...> ON UPDATE SET NULL
...> ON DELETE SET NULL
...> );
sqlite> INSERT INTO os (os_name, os_group)
...> VALUES('NetBSD', 1);
sqlite> INSERT INTO os (os_name, os_group)
...> VALUES('Slackware', 2);
sqlite> INSERT INTO os (os_name, os_group)
...> VALUES('Winders', 3);
sqlite> DELETE FROM os_groups WHERE os_group = 3;
sqlite> SELECT * FROM os;
1
2
3
I was expecting the `3
The os_group should have been set to NULL.
NetBSD laptop 9.0 NetBSD 9.0 (GENERIC) #0: Fri Feb 14 00:06:28 UTC
2020
mkrepro%mkrepro.NetBSD.org@localhost:/usr/src/sys/arch/amd64/compile/GENERIC amd64
Thanks,
Edgar