General many-to-many relationship problem ( Postgresql )

Question

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

Answer 1

If you want the auction_values to be reused by different auctions, you should declare a constraint the other way round:

ALTER TABLE auctions
ADD CONSTRAINT fk_auction_values
FOREIGN KEY (auction_value_key)
REFERENCES auction_values (id)

Answer 2

You need an extra table to model a many-to-many relationship. It will contain the mappings between auctions and auction_values. It needs two columns: auction_id and auction_value_id.

Answer 3

Quoting the wikipedia

In the context of relational databases, a foreign key is a referential constraint between two tables.1 The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

As Quassnoi points out, it sounds as if you want to have multiple rows in auctions reference single rows in auction_values.

For that the master or referenced table is auction_values and child or referencing table is auction_values.

If on the other hand Alex is right and you want to reference multiple rows in the auction_values you will need another table.

This table will help you convert the many-to-many relationship (which can not be directly realized on the physical database level) to two one-to-many relationships.

Generally you could have this table store ids from the two starting tables and in this way you can associate any combination of the records from auction_values and auctions.

However, this might be too general and you might actually be after a table auction_value_keys (auction_value_key)