CODEWITHSUNDEEP
sqldatabasepostgresqldata-structures
IndiaSke
IndiaSke

Reputation: 358

How to manage postgresql foreign keys?

I need some advice on SQL structure on Postgresql.

I have those two tables :

DROP TABLE IF EXISTS "public"."attribute_value";

CREATE TABLE "public"."attribute_value"
(
"id"                        INTEGER NOT NULL,
"attribute_id"              INTEGER NOT NULL,
"value"                     CHARACTER VARYING(100) NULL

);

--*****************************************************

DROP TABLE IF EXISTS "public"."product_attribute";

CREATE TABLE "public"."product_attribute"
(
"product_id"                INTEGER NOT NULL,
"attribute_value_id"        INTEGER NOT NULL,
"attribute_id"              INTEGER NOT NULL
);

I added no constraints on purpose. I need a foreign key on the child table product_attribute.attribute_value_id referencing the parent table attribute_value.id. The best practice is to create a primary key on the field attribute_value.id (maybe with a sequence), or to CREATE UNIQUE INDEX on attribute_value.id ? I first thought indexes were only special lookup tables that the database search engine can use to speed up data retrieval. But when I played with foreign keys, I found that creating an unique index allowed me to avoid error "there is no unique constraint matching given keys for referenced table blablabla" because a foreign key is not supposed to point to a non unique value. Should indexes be used to create foreign keys then ?

I also need a foreign key on the child table product_attribute.attribute_id referencing parent table attribute_value.attribute_id. The problem is that attribute_value.attribute_id is not unique. But all the rows in product_attribute.attribute_id must not take any value out of attribute_value.attribute_id's possible values. How should I do ?

Upvotes: 1

Views: 268

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246918

Every table should have a primary key. Don't join the legion of people who complain about duplicate rows in their tables.

So make id the primary key of attribute_value, then you can create a foreign key constraint.

Constraints are implemented by unique indexes, so technically they are almost the same. Still you need a constraint and not just a unique index as the target of a foreign key constraint.

About attribute_id: that should not be a foreign key constraint between the two tables from your question, but both tables should have a foreign key referencing yet another table (attribute?).

Upvotes: 1

Related Questions