> 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
Mark
--
Mark Lawrence
