Re: foreign SERIAL keys in weak entity primary keys - Mailing list pgsql-general

> 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