Re: foreign SERIAL keys in weak entity primary keys

On Mon, 10 Mar 2003, Thomas Hood wrote:

> CREATE TABLE Items (
> IID SERIAL PRIMARY KEY, --Item ID
> Name TEXT NOT NULL, -- Item name
> SID INTEGER REFERENCES Suppliers); --supplier

> CREATE TABLE Inventory (
> IID SERIAL REFERENCES Items,
> PackSize INTEGER NOT NULL, --no. of items in a pack
> QOH INTEGER NOT NULL, --quantity of this size pack(of this item) on shelf.
> WID SERIAL REFERENCES Warehouses, --warehouse where shelved
> Price DECIMAL(5,2) NOT NULL,
> PRIMARY KEY (IID, PackSize));

> The table Inventory has tuples which have minimal candidate key of (IID,
> PackSize), yet for some reason it insists on making IID * WID unique
> columns!

Why do you need sequences for Inventory.IID and Inventory.WID. Just make
them into integers that references the other tables. By declating them as
SERIAL you say that you want sequences generated for these columns. You
already have a sequence that generate Items.IID, you don't need another
one.

--
/Dennis