foreign SERIAL keys in weak entity primary keys

Поиск
Список
Период
Сортировка
От Thomas Hood
Тема foreign SERIAL keys in weak entity primary keys
Дата
Msg-id 002801c2e69c$90582e60$5021c350@2x600mhzraid
обсуждение исходный текст
Ответы Re: foreign SERIAL keys in weak entity primary keys  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: foreign SERIAL keys in weak entity primary keys  (Dennis Björklund <db@zigo.dhs.org>)
Список 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






В списке pgsql-general по дате отправления:

Предыдущее
От: Joe Conway
Дата:
Сообщение: Re: cross tab confusion
Следующее
От: Tom Lane
Дата:
Сообщение: Re: foreign SERIAL keys in weak entity primary keys