On Sun, Jan 11, 2015 at 12:21 PM, Rich Shepard <rshep...@appl-ecosys.com>
wrote:
> 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.
>
> Rich
>
It might be a tad easier to give an exact answer if you could post the DDL
which creates the tables. But basically, you can't do what you want
directly. Can the "item ID" value in the Purchases and Donations tables be
duplicated? If so, then I would suggest making them unique, perhaps by
prefixing them with a D and a P for "Donation" an "Purchase" respectively.
But, really, what _I_ personally would try to do is to factor out the
common columns in Purchases and Donations into a "items" table which would
then be the primary key in the Donations, Purchases, and Raffles tables.
This column would also be a foreign key in those table. Perhaps something
like:
CREATE TABLE All_Items (itemnbr integer primary key, Description text,
DonatedOrPurchased char(1) NOT NULL CHECK(DonatedOrPurchased in ('D','P'));
CREATE TABLE Donations (itemnbr integer primary key references All_Items
(itemnbr) ON DELETE CASCADE,
--- other Donations unique columns
);
CREATE TABLE Purchases (itemnbr integer primary key
references All_Items (itemnbr) ON DELETE CASCADE,
-- other Purchases unique columns
);
CREATE TABLE Raffles (itemnbr integer primary key reference All_Items
(itemnbr) ON DELETE CASCADE,
-- other Raffles columns
);
--
While a transcendent vocabulary is laudable, one must be eternally careful
so that the calculated objective of communication does not become ensconced
in obscurity. In other words, eschew obfuscation.
111,111,111 x 111,111,111 = 12,345,678,987,654,321
Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
