Another consideration: it is possible that two different anonymous books are indeed different books. It is also possible a given "author" releases a new rewritten book with the same name. I'm thinking of the Hardy Boys series in particular from my personal experience. Not a big deal, but thought I'd share it. On Aug 21, 2015 7:29 AM, "Stephen Chrzanowski" <pontiac76 at gmail.com> wrote:
> Another option would be to not use NULL but use an empty string. > > On Fri, Aug 21, 2015 at 9:17 AM, R.Smith <rsmith at rsweb.co.za> wrote: > > > > > > > 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 > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
