I'm developing a COOKBOOK DB in PostgreSQL. I can’t build the PRICE table. I have a Cookbook ERD. There are three factoring tables relating to PRICE: INGREDIENT, SUBSTITUTION, and PRICE.
ERD (notice the 1:M for INGREDIENT to SUBSTITUTION): [ERD]
I can have one INGREDIENT with many SUBSTITUTIONS and a one-to-one between SUBSTITUTION and PRICE (one PRICE per SUBSTITUTION if a PRICE is known). If the PRICE is known then the PRICE is able to define a tuple with a composite primary key: (price_id, ingredient_id (fk), substitution_id (fk)).
PostgrSQL is not allowing this relationship. I set the keys in SUBSTITUTION to have a UNIQUE constraint so that shouldn’t be the problem. The ingredient_id in SUBSTITUTION is a foreign key to INGREDIENT and therefore may not be in SUBSTITUTION but the error doesn't suggest that:
cookbook=# \d+ SUBSTITUTION
Table public.substitution
Column | Type | Modifiers
-------------------+-----------------------+--------------------------------------------------------------------------+----------+
substitution_id | integer | not null default nextval('subsitution_substitution_id_seq'::regclass) | plain |
ingredient_id | integer | not null default nextval('subsitution_ingredient_id_seq'::regclass) | plain |
name | character varying(50) | not null | extended |
measurement_ref_id | integer | not null default nextval('subsitution_measurement_ref_id_seq'::regclass) | plain |
metric_unit | character varying(25) | not null | extended |
Indexes:
"subsitution_pkey" PRIMARY KEY, btree (substitution_id, ingredient_id)
"uniqueattributes" UNIQUE, btree (substitution_id, ingredient_id)
Foreign-key constraints:
"subsitution_ingredient_id_fkey" FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
"subsitution_measurement_ref_id_fkey" FOREIGN KEY (measurement_ref_id) REFERENCES measurement_ref(measurement_ref_id)
Has OIDs: no
cookbook=# create table price(
price_id serial not null,
ingredient_id serial references substitution(ingredient_id),
cookbook(# substitution_id serial references substitution(substitution_id),
cookbook(# usdollars smallint not null,
cookbook(# availability season,
cookbook(# seasonal boolean,
cookbook(# primary key (price_id, ingredient_id, substitution_id)
cookbook(# );
NOTICE: CREATE TABLE will create implicit sequence "price_price_id_seq" for serial column "price.price_id"
NOTICE: CREATE TABLE will create implicit sequence "price_ingredient_id_seq" for serial column "price.ingredient_id"
NOTICE: CREATE TABLE will create implicit sequence "price_substitution_id_seq" for serial column "price.substitution_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "price_pkey" for table "price"
ERROR: there is no unique constraint matching given keys for referenced table "substitution"
I've omitted some columns, so you can focus on the keys.
create table ingredients (
ingredient_id serial primary key,
-- Don't allow duplicate names.
ingredient_name varchar(35) not null unique
);
create table substitutions (
-- These are properly declared integer, not serial.
-- Also note two separate foreign key references.
ingredient_id integer not null references ingredients (ingredient_id),
substitute_id integer not null references ingredients (ingredient_id),
primary key (ingredient_id, substitute_id)
);
create table prices (
-- Price id number is unnecessary.
ingredient_id integer not null,
substitute_id integer not null,
-- Money is usually declared numeric(n, m) or decimal(n, m).
us_dollars numeric(10, 2) not null
-- Negative amounts don't make sense.
check (us_dollars >= 0),
-- Only one row per distinct substitution.
primary key (ingredient_id, substitute_id),
-- One single foreign key reference, but it references *two* columns.
foreign key (ingredient_id, substitute_id) references substitutions (ingredient_id, substitute_id)
);