On Monday, 24 Aug 2015 2:46 AM -0400, Mark Lawrence wrote: >> 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. > > You can achieve this using a partial index[1] on the Books.title > column, which is used only when the author is null. A test script to > demonstrate: > > 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 <> '') > ); > > -- ************************************************** > create unique index no_author_index on Books(title) > where author is null; > -- ************************************************** > > insert into Authors values(1,'A. Jones'); > insert into Authors values(2,'T. Smith'); > > insert into Books values(1, 'History of Scotland', 1); > insert into Books values(2, 'History of Scotland', 2); > insert into Books values(3, 'Manual of DOS', NULL); > > select > b.title, a.name > from > Books b > left join > Authors a > on > a.id = b.author > ; > -- title name > -- ------------------- ---------- > -- History of Scotland A. Jones > -- History of Scotland T. Smith > -- Manual of DOS NULL > > insert into Books values(4, 'Manual of DOS', NULL); > -- Error: near line 37: UNIQUE constraint failed: Books.title > > [1] https://www.sqlite.org/partialindex.html
Yes, this works and seems to me to be more elegant than my original idea of replacing a NULL author field with a 0, so thanks to you and to the others who responded with their suggestions. -- Will
