I'm working on a program that involves a catalogue of books. Part of
the database schema looks like this:
create table Books(id integer primary key,
title text collate nocase not null,
author references Authors(id),
...
unique(title, author));
create table Authors(id integer primary key,
name text unique not null check(name <> ''));
The idea here is that the title+author of a book must be unique, but a
book may not necessarily have an author associated with it. But, the
schema fragment as I have presented it does not disallow entering the
same title with a null author multiple times, which is clearly
undesirable.
In thinking about how to avoid this, one possibility that has occurred
to me is to add an entry to the Authors table with id=0, name=NULL, and
to modify the Books table to be:
create table Books(id integer primary key,
title text collate nocase not null,
author references Authors(id) not null,
...
unique(title, author));
With this, entries in the Books table that presently have the author
field set to NUll would instead have author=0.
What I would like to have is a trigger that when an attempt is made to
enter a new record into the Books table with a NULL author field, is
to force the author field to 0 instead. I can't see how to do this
with a "before insert" trigger, though. Perhaps I'm approaching this
the wrong way; in any case I'd appreciate advice.
--
Will
