Thread: foreign SERIAL keys in weak entity primary keys

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

"Thomas Hood" <s0096184@sms.ed.ac.uk> writes:
> 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!

In versions before 7.3, SERIAL implies UNIQUE.  If you don't like that,
update ...

            regards, tom lane

On Mon, 10 Mar 2003, Thomas Hood wrote:

> CREATE TABLE Items (
>  IID SERIAL PRIMARY KEY, --Item ID
>  Name TEXT NOT NULL, -- Item name
>  SID INTEGER REFERENCES Suppliers); --supplier

> 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));

> 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!

Why do you need sequences for Inventory.IID and Inventory.WID. Just make
them into integers that references the other tables. By declating them as
SERIAL you say that you want sequences generated for these columns. You
already have a sequence that generate Items.IID, you don't need another
one.

--
/Dennis

> 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));

Why do you need Identity/Sequence Constratints for Reference Keys.
If any sequence missed in Item table then reference table can not recognise
that keys, or there may be change of refering another key of Item table
which you may not expect. so you can change your Inventory table like this.

> CREATE TABLE Inventory (
* >  IID integer 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 integer REFERENCES Warehouses, --warehouse where shelved
>  Price DECIMAL(5,2) NOT NULL,
>  PRIMARY KEY (IID, PackSize));

Regards,
Sreedhar


----- Original Message -----
From: "Thomas Hood" <s0096184@sms.ed.ac.uk>
To: <pgsql-general@postgresql.org>
Sent: Monday, March 10, 2003 6:02 AM
Subject: [GENERAL] foreign SERIAL keys in weak entity primary keys


> 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
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org