On Sunday, 11 January, 2015 11:22, Rich Shepard <[email protected]> said:
> Items to be raffled can be donated or purchased. The Donations and
>Purchases tables each have the item ID as their PK.
> The Raffles table should have the PK as either the Donations or the
>Purchases item ID. How do I write the DDL creating the Raffles table so
>that the PK is either a donated or purchased item_id number? I've only
>had a foreign key reference one specific table, not either of two tables
>and I've not found a solution in the references available to me.
As already said, factor out the common item information into an Item table, the
Donation information into a Donated table, and the Purchase information into a
Purchased table. Set up the schema as suggested by John.
CREATE TABLE Item
(
itemnbr integer primary key,
Description text collate nocase,
DonatedOrPurchased text collate nocase NOT NULL CHECK(DonatedOrPurchased
in ('D','P')
);
CREATE TABLE Donated
(
itemnbr integer primary key references Item on delete cascade,
--- other Donated unique columns
);
CREATE TABLE Purchased
(
itemnbr integer primary key references Item ON DELETE CASCADE,
-- other Purchased unique columns
);
CREATE VIEW Items
as
select *
from Items
left join Donated using (Itemnbr)
left join Purchased using (Itemnbr);
CREATE TRIGGER Items_Delete INSTEAD OF DELETE on Items
begin
delete from Item where Item.Itemnbr = OLD.Itemnbr;
end;
CREATE TRIGGER Items_InsertD INSTEAD OF INSERT on Items
WHEN NEW.DonatedOrPurchased == 'D'
begin
insert into Item values (NEW.Itemnbr, NEW. ...) -- for all fields that are
part of the Item table
insert into Donated values (NEW.Itemnbr, NEW. ...); -- for all fields that are
part of the Donated table
END;
CREATE TRIGGER Items_InsertP INSTEAD OF INSERT on Items
WHEN NEW.DonatedOrPurchased == 'P'
begin
insert into Item values (NEW.Itemnbr, NEW. ...) -- for all fields that are
part of the Item table
insert into Purchased values (NEW.Itemnbr, NEW. ...); -- for all fields that
are part of the Purchased table
END;
Of course, you can also just always interd and update manually and use the
Items view to get all the information for the item.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
