foreign SERIAL keys in weak entity primary keys

From: "Thomas Hood" <s0096184(at)sms(dot)ed(dot)ac(dot)uk>
To: <pgsql-general(at)postgresql(dot)org>
Subject: foreign SERIAL keys in weak entity primary keys
Date: 2003-03-10 00:32:42
Message-ID: 002801c2e69c$90582e60$5021c350@2x600mhzraid
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yudha Setiawan 2003-03-10 04:02:54 OOT-Does PostgreSQL has commercial level.
Previous Message Justin Clift 2003-03-10 00:15:25 Re: [GENERAL] division by zero