design problem involving trigger

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

design problem involving trigger

Will Parsons
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.

--
Will

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

R Smith


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Stephen Chrzanowski
Another option would be to not use NULL but use an empty string.

On Fri, Aug 21, 2015 at 9:17 AM, R.Smith <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Scott Robison-2
Another consideration: it is possible that two different anonymous books
are indeed different books. It is also possible a given "author" releases a
new rewritten book with the same name. I'm thinking of the Hardy Boys
series in particular from my personal experience. Not a big deal, but
thought I'd share it.
On Aug 21, 2015 7:29 AM, "Stephen Chrzanowski" <[hidden email]> wrote:

> Another option would be to not use NULL but use an empty string.
>
> On Fri, Aug 21, 2015 at 9:17 AM, R.Smith <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Hick Gunter
In reply to this post by Will Parsons
Are you looking for "NOT NULL DEFAULT 0"?

-----Urspr√ľngliche Nachricht-----
Von: Will Parsons [mailto:[hidden email]]
Gesendet: Freitag, 21. August 2015 04:47
An: [hidden email]
Betreff: [sqlite] design problem involving trigger

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.

--
Will

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Will Parsons
In reply to this post by R Smith
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Will Parsons
In reply to this post by Scott Robison-2
On 21 Aug 2015, Scott Robison wrote:
> Another consideration: it is possible that two different anonymous books
> are indeed different books. It is also possible a given "author" releases a
> new rewritten book with the same name. I'm thinking of the Hardy Boys
> series in particular from my personal experience. Not a big deal, but
> thought I'd share it.

Yes, it's a theoretical possibility, but in that case I'd have to
force some difference into the title field to make a distinction.

--
Will

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

R Smith
In reply to this post by Will Parsons


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?

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.

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.

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.

A note on NULL and why: NULL + NULL != 2(NULL).

NULL is not a valid value, in fact it isn't a value at all, it is not
meant to appear in anything data related barring to indicate a fault or
voidness. You can't have operations on non-values. If you want a
constraint or perhaps another sort of function to work on some data,
make sure it is actual data and not NULL.

To put this philosophically: "Infinity" is a much more precise value
than "NULL".

This also means that NULL is not equal to NULL, it equals nothing
because it cannot be compared, even to other NULLs - In DB theory
anyway[1]. Sadly many DB engines have kind of circled around this
problem a bit by allowing NULL to do all kinds of things it shouldn't.
I'm happy though that for UNIQUE constraints in some DBs (and SQLite at
least) a NULL is not equal to any other value, including other NULLs.



Apologies for the ramble, hope some of it helps.
Ryan


[1] - I don't actually see the reason for 3-value logic, NULL is a
construct with origins in DB engine design, not relational theory. It's
useful to have a word to indicate "emptiness" or more mathematically
correct: "The empty set", but beyond that, any operational use of it is
questionable.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Will Parsons
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Barry Smith
In reply to this post by R Smith
Could this not be achieved by two indexes: one partial and one complete?

CREATE UNIQUE INDEX idx_books1 ON Books(title, author);

CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL;

To save space and (maybe) time, you could put a 'WHERE author NOTNULL' on the first index.

Of course, I'm just talking about how to code it, the issues mentioned by R Smith is a different kettle of fish.

Cheers,

Barry


> On 23 Aug 2015, at 3:05 am, "R.Smith" <[hidden email]> 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?
>
> 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.
>
> 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.
>
> 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.
>
> A note on NULL and why: NULL + NULL != 2(NULL).
>
> NULL is not a valid value, in fact it isn't a value at all, it is not meant to appear in anything data related barring to indicate a fault or voidness. You can't have operations on non-values. If you want a constraint or perhaps another sort of function to work on some data, make sure it is actual data and not NULL.
>
> To put this philosophically: "Infinity" is a much more precise value than "NULL".
>
> This also means that NULL is not equal to NULL, it equals nothing because it cannot be compared, even to other NULLs - In DB theory anyway[1]. Sadly many DB engines have kind of circled around this problem a bit by allowing NULL to do all kinds of things it shouldn't. I'm happy though that for UNIQUE constraints in some DBs (and SQLite at least) a NULL is not equal to any other value, including other NULLs.
>
>
>
> Apologies for the ramble, hope some of it helps.
> Ryan
>
>
> [1] - I don't actually see the reason for 3-value logic, NULL is a construct with origins in DB engine design, not relational theory. It's useful to have a word to indicate "emptiness" or more mathematically correct: "The empty set", but beyond that, any operational use of it is questionable.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

R Smith


On 2015-08-23 03:32 AM, Barry Smith wrote:

> Could this not be achieved by two indexes: one partial and one complete?
>
> CREATE UNIQUE INDEX idx_books1 ON Books(title, author);
>
> CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL;
>
> To save space and (maybe) time, you could put a 'WHERE author NOTNULL' on the first index.
>
> Of course, I'm just talking about how to code it, the issues mentioned by R Smith is a different kettle of fish.
>
> Cheers,
>
> Barry

Yes this will work for what the OP wanted, I think.  Great suggestion,
to prove the concept, consider:

       -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
       -- Script Items: 10         Parameter Count: 0 SQLitespeed v2.0.1

       -- 2015-08-23 04:50:41.253  |  [Info]       Script Initialized,
    Started executing...
       --
    ================================================================================================

    CREATE TABLE tA(c1 TEXT, c2 TEXT, c3 TEXT);

    CREATE UNIQUE INDEX tAI1 ON tA(c1, c2);

    CREATE UNIQUE INDEX tAI2 ON tA(c1) WHERE c2 IS NULL;

    INSERT INTO tA VALUES ('ABC', 'Joe',  '1');

    INSERT INTO tA VALUES ('ABC', 'John', '2');

    INSERT INTO tA VALUES ('ABC', 'Jim',  '3');

    INSERT INTO tA VALUES ('ABC', NULL,   '4');

    INSERT INTO tA VALUES ('ABC', NULL,   '5');

       -- 2015-08-23 04:50:41.263  |  [ERROR]      UNIQUE constraint
    failed: tA.c1
       -- 2015-08-23 04:50:41.264  |  [Info]       Script failed -
    Rolling back...
       -- 2015-08-23 04:50:41.264  |  [Success]    Transaction Rolled back.
       -- 2015-08-23 04:50:41.264  |  [ERROR]      Failed to complete:
    Script Failed in Item 7: UNIQUE constraint failed: tA.c1
       -- -------  DB-Engine Logs (Contains logged information from all
    DB connections during run)  ------
       -- [2015-08-23 04:50:41.226] APPLICATION : Script
    D:\Documents\SQLiteAutoScript.sql started at 04:50:41.226 on 23 August.
       -- [2015-08-23 04:50:41.263] ERROR (2067) : abort at 15 in
    [INSERT INTO tA VALUES ('ABC', NULL,   '5');]: UNIQUE constraint
    failed: tA.c1
       --
    ================================================================================================



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

nomad
In reply to this post by Will Parsons
> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

nomad
On Mon Aug 24, 2015 at 08:46:57AM +0200, Mark Lawrence wrote:
>
> 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

My apologies. It appears from the mailing list archive this was already
mentioned, but I didn't see those messages in my inbox.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: design problem involving trigger

Will Parsons
In reply to this post by nomad
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users