Re: sqlite3 issues

[Edgar Pettijohn <edgar%pettijohn-web.com@localhost>]

On Feb 17, 2020 6:37 AM, Roy Marples <roy%marples.name@localhost> wrote:
>
> On 17/02/2020 02:34, Edgar Pettijohn wrote:
> > On 02/16/20 20:31, Edgar Pettijohn wrote:
> >> 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
> >>
> > Not sure why but the last bit got eaten along the way.
> > 
> > sqlite> select * from os;
> > 1
> > 2
> > 3
> > 
> > I was expecting the `3
>
> Have you enabled foreign key support at the application?
> https://www.sqlite.org/foreignkeys.html
> sqlite> PRAGMA foreign_keys = ON;
>
> Roy

That may be it.

Thanks,

Edgar