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: | Whole Thread | Raw Message | 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
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 |