July 26th, 2003, 05:53 PM
|
|
Not Yet Perfect
|
|
Join Date: Nov 2002
Location: Squamish, BC
Posts: 111
Time spent in forums: < 1 sec
Reputation Power: 6
|
|
After doing some research, this is apparently a known long-standing issue with inheritance in Postgres. Nobody calls it a "bug" outright, though. Just a limitation. It makes me wonder how others have tackled problems of a similar nature.
I've since devised a different way, but if you guys have any other suggestions for how you've handled this sort of thing in PostgreSQL, let me know.
To grossly simplify, here's what I was trying to do.
Code:
CREATE TABLE products (
id serial NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
price numeric(6,2)
);
CREATE TABLE pants (
waist smallint,
length smallint,
colour varchar(12)
) inherits (products);
CREATE TABLE computers (
cpu varchar(12),
mhz smallint,
) inherits (products);
INSERT INTO pants (name,price,waist,length,colour)
VALUES ('Brand-X Cargo Pants', 49.95, 32, 34, 'khaki');
INSERT INTO computers (name,price,cpu,mhz)
VALUES ('Flower Power iMac', $666.66, 'Motorola 750', 500);
SELECT id,name FROM products;
--> 1 Brand-X Cargo Pants
--> 2 Flower Power iMac
INSERT INTO computers (id, name,price,cpu,mhz)
VALUES (1, 'Mac Plus', $5.00, 'Motorola 68000', 8);
SELECT id,name FROM products;
--> 1 Brand-X Cargo Pants
--> 1 Mac Plus
--> 2 Flower Power iMac
Huh? But products.id is a primary key! The "Mac Plus" screwed it up by inserting duplicate values!
No, I'm not actually making a database for a 24hr Pants-and-Computers store, but I'm using this department store analogy to show how different records will need different sets of attributes, yet I would still need to select from all of them as "products" and maintain referential integrity.
Ideas and discussion welcome...
|
Dev Articles Community Forums Sponsor:
PostgreSQL Inheritance Constraints
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
