foreign SERIAL keys in weak entity primary keys

"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,