I have the following:
CREATE TABLE Items (
IID SERIAL PRIMARY KEY, --Item ID
Name TEXT NOT NULL, -- Item name
SID INTEGER REFERENCES Suppliers); --supplier
and...
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));
My problem is that it does this
psql:store.sql:40: NOTICE: CREATE TABLE will create implicit sequence
'inventory_iid_seq' for SERIAL column 'inventory.iid'
psql:store.sql:40: NOTICE: CREATE TABLE will create implicit sequence
'inventory_wid_seq' for SERIAL column 'inventory.wid'
psql:store.sql:40: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'inventory_pkey' for table 'inventory'
psql:store.sql:40: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'inventory_iid_key' for table 'inventory'
^^^^^^^^^^^^^^
psql:store.sql:40: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'inventory_wid_key' for table 'inventory'
^^^^^^^^^^^^^^^
psql:store.sql:40: NOTICE: CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
Which means that I cannot do the following:
INSERT INTO Inventory (IID, PackSize, QOH, Price, WID) VALUES (1, 1, 6, 0.5,
1);
INSERT 990894 1
INSERT INTO Inventory (IID, PackSize, QOH, Price, WID) VALUES (1, 2, 0, 0.9,
1);
psql:store.sql:80: ERROR: Cannot insert a duplicate key into unique index
inventory_iid_key
INSERT INTO Inventory (IID, PackSize, QOH, Price, WID) VALUES (2, 1, 0, 2.0,
1);
psql:store.sql:87: ERROR: Cannot insert a duplicate key into unique index
inventory_wid_key
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!
How can I get round this?
TIA,
Thomas Hood
