On 2015-08-21 04:47 AM, Will Parsons wrote:
> 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.
>
Nothing wrong with your approach, simply declare the Author to be NOT
NULL and if it is possible to have non-Authors you could do what you
suggest, add a 0-Author to the parent table, or, you could use a partial
Index (available after SQLite 3.7 only I think) which would allow you to
have NULLS but still maintain a strict relation, something like this:
create table Books(id integer primary key,
title text collate nocase not null,
author int not null references Authors(id),
...
);
create unique index uBookAuth on Books(title,author) where author is not null;
create table Authors(id integer primary key,
name text unique not null check(name <> ''));
Read more at:
http://www.sqlite.org/partialindex.html
