Is FileType case sensitive?
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski >Sent: Saturday, 10 February, 2018 22:35 >To: General Discussion of SQLite Database >Subject: [sqlite] Different kind of constraint > >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); >_______________________________________________ >sqlite-users mailing list >email@example.com >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list firstname.lastname@example.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users