On 21 Aug 2015, R.Smith 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 <> ''));
I guess I don't understand how having a partial index where author is not null prevents adding two entries with the same title and a null author. How is this different from what I have now with the "unique(title, author)" specification? -- Will
