On 02/11/2018 12:34 PM, Stephen Chrzanowski wrote:
I've been mucking with this for the last half hour or so.
I have a table which is kind of in a unique situation.
In the app I was talking about earlier, I have a table that will contain
BLOB that has FK data referencing a magazine table. This blob-tables
responsibility is going to hold the actual contents of the files I'm going
to give it, so, floppy disk images, or, pictures, or what have you. Any
file can be attached to this one particular magazine, BUT, I only want a
single COVER image stored in this table for that one magazine, and I'd like
SQLite to handle ensuring that this happens.
In reference to the magazine table itself, instead of putting a BLOB field
on the actual Magazine table, or creating a table JUST for cover images,
I'd like to have a unique constraint on the table in that I can have the
same MagazineID and FileType fields, EXCEPT, I can only have one MagazineID
with one FileType='Cover' entry.
In other words, I can never have more than a single
MagazineID=1,FileType='Cover' entry, but, I can have as many
MagazineID=1,FileType='Image' and as many MagazineID=1,FileType='D64' rows
with their relevant blob info.
What I'm doing in code right now is deleting MagazineID=1,FileType='Cover'
before doing an insert on the table, then update the blob field with the
image, but, if I can set this up to be an Insert Or Replace function,
that'd be better on me.
The schema for the table in question is pretty simple:
CREATE TABLE [_MagazineContent](
[MagazineID] INTEGER NOT NULL REFERENCES [Magazines]([MagazineID]) ON
DELETE CASCADE,
[Description] CHAR,
[FileType] CHAR NOT NULL,
[Content] BLOB);
How about:
CREATE UNIQUE INDEX i1 ON MagazineContent(MagazineID) WHERE
FileType='Cover';
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
