General many-to-many relationship problem ( Postgresql )
- by David
Hi,
i have two tables:
CREATE TABLE "public"."auctions" (
"id" VARCHAR(255) NOT NULL,
"auction_value_key" VARCHAR(255) NOT NULL,
"ctime" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"mtime" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "pk_XXXX2" PRIMARY KEY("id"),
);
and
CREATE TABLE "public"."auction_values" (
"id" NUMERIC DEFAULT nextval('default_seq'::regclass) NOT NULL,
"fk_auction_value_key" VARCHAR(255) NOT NULL,
"key" VARCHAR(255) NOT NULL,
"value" TEXT,
"ctime" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"mtime" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "pk_XXXX1" PRIMARY KEY("id"),
);
if i want to create a many-to-many relationship on the auction_value_key like this:
ALTER TABLE "public"."auction_values"
ADD CONSTRAINT "auction_values_fk" FOREIGN KEY ("fk_auction_value_key")
REFERENCES "public"."auctions"("auction_value_key")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
i get this SQL error:
ERROR: there is no unique constraint matching given keys for referenced table "auctions"
Question:
As you might see, i want "auction_values" to be be "reused" by different auctions without duplicating them for every auction... So i don't want a key relation on the "id" field in the auctions table...
Am i thinking wrong here or what is the deal? ;)
Thanks
