update as opposed to insert. There is a difference.
Once you've created a DB with at least 1 table, it should not surprise you that the DB is empty.
You have to insert data into a table and create a record. If that table contains more that 1 record, you have a choice (depending on whether NULL is allowed) on whether to enter data into that field or not.
In our example here, we've already added a new book, author, and some keywords. But we didn't have all the information for some of the tables. We'll fix that now.
Our book was published in 2026 and has an ISBN of 9457861237150.
sqlite $myDB << sql update titles set pubyear=2026; update titles set isbn=9457861237150; sql
Confirmation:
select * from titles; id authorid titleid title pubyear isbn -- -------- ------- --------------- ------- ------------- 1 10 10 Nother Book (A) 2026 9457861237150
What happens if we just insert pubyear and isbn?
insert into titles(authorid,titleid,pubyear,isbn)values(10,10,2026,9457861237150); Runtime error: NOT NULL constraint failed: Titles.title (19)
Hmmm. We have to include ALL the fields in our attempt to just add the 2 missing items.
sqlite $myDB << sql insert into titles( authorid, titleid, pubyear, isbn, title) values(10,10,2026,9457861237150,'Nother Book (A)'); sql select * from titles; id authorid titleid title pubyear isbn -- -------- ------- --------------- ------- ------------- 1 10 10 Nother Book (A) 2026 9457861237150 2 10 10 Nother Book (A) 2026 9457861237150
Oh oh. Now we have 2 records exactly the same. NOT GOOD
Have to delete one of them ...
sqlite $myDB << sql delete from titles where id=2; sql select * from titles; id authorid titleid title pubyear isbn -- -------- ------- --------------- ------- ------------- 1 10 10 Nother Book (A) 2026 9457861237150
This is why we update instead of insert.
We also have a minor fix for the keywords table. I use bash, but the correct usage is BASH, because it is actually an anagram for Bourne Again SHell, named after Stephen / Steven Bourne, the author of the Bourne shell in 1979.
sqlite $myDB << sql update keywords set keyword='BASH' where keyword='bash';
We could also have used the id number - they are unique to each record:
update keywords set keyword='BASH' where id=2; select * from keywords order by keyword; id authorid titleid keyword -- -------- ------- ----------- 2 10 10 BASH 4 10 10 Database 1 10 10 Programming 3 10 10 SQLite
Note the order of the keywords has changed now that they all begin with an upper-case letter.