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
Re: foreign SERIAL keys in weak entity primary keys |
Список | 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 по дате отправления: