On Saturday, 22 Aug 2015 1:05 PM -0400, R.Smith wrote: > > > On 2015-08-21 11:23 PM, Will Parsons wrote: >> 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? >> > > I think I may have misinterpreted slightly... > > To clarify: are the book titles unique or are they not?
No - the combination title + author is unique. > If they are Unique, i.e if no book title can ever appear twice, > regardless who authored it, then simply declare a unique index on title. > If they are NOT Unique, i.e. if a title can appear more than once, then > why is it not working for you? > > Essentially, if I interpret correctly, you are asking the DB to NOT > limit the number of same-titled books, except when you have supplied an > author and the same author has already such a titled book, but then when > you don't supply an author, it should know to now also limit the copies > of no-author books? > > I assumed before that you only added NULL for author if you don't know > the author yet (which would make sense and can later be updated) but > then you can't force the unique constraint, there may be many books with > coinciding titles and not-yet-known authors. The use of NULL as author is not for a case where the author is presently unknown, but where there is no meaningful author. > Reading again, I am now thinking that's not the case, you might add NULL > authors to books which simply don't have authors (well, all books have > authors, but the author might be unknown and accepted to be of unknown > status for time to come), in which case, there might be many same-titled > unknown-author books. It's theoretically possible, but in that case I would be content to force a difference in the title. It should be possible to have the following: 'History of Scotland' | -> 'A. Jones' 'History of Scotland' | -> 'T. Smith' 'Manual of DOS' | NULL But, an attempt to insert another record 'Manual of DOS' with a NULL author should fail. (In the above, I've used "->" to indicate the author field is actually a foreign key reference to another table.) It *is* possible to have the same book in more than one copy, perhaps a hardcover and a softcover edition, but in this case I would handle by appending to the title some differentiation, e.g., 'The Divine Comedy (deluxe edition)'. > If this is the case and you still want to limit unknown author books to > just 1 instance, I would suggest to use an explicit author name, maybe > something like "(by Unknown)" which would be happily subdued by the > Unique constraint if violated. Another reader suggested the empty > string, which will also count as a valid author and do the limiting you > require. Your own suggestion of using a zero ID in stead will also do > the job just fine. In fact, this is the only reason we'd ever put NULL > into a field such as this, precisely so that the Unique constraint is > not forced, because for any valid value, it will be. Well, it seems that NULL is semantically the most appropriate, but it has the disadvantage (in this situation) of not forcing the Unique constraint. I don't like forcing an author name. I wouldn't want to end up with e.g., both: 'Manual of DOS' | -> '(by Unknown)' 'Manual of DOS' | -> '(no author)' My original query was on possibly using a trigger to force an attempt to insert a record with an author field of NULL to 0 instead. If a trigger is not the way to do this, I'll have to do it at the application level instead. -- Will
