Are you looking for "NOT NULL DEFAULT 0"?
-----Urspr?ngliche Nachricht-----
Von: Will Parsons [mailto:varro at nodomain.invalid]
Gesendet: Freitag, 21. August 2015 04:47
An: sqlite-users at mailinglists.sqlite.org
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
sqlite-users at mailinglists.sqlite.org
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: hick at scigames.at
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.
