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 >
