Today I received mail from one of my clients:
I am using MicroOLAP Database Designer to develop a ERD for a PostGIS database. In my opinion one drawback to PgMDD is the lack of an explicit cardinality determination on the relationship. It is not obvious how to declare two tables in a IS_A relationship (1:1) and there is no sufficient explanation in the help section.
Would you be so kind as to support me with some information how I can determine a 1:1 cardinality on a relation.
I don’t want to discuss the reasons why one may need 1-to-1 relationship design. I just want to propose solution for it. Trick is simple, I suppose. All we need is to add unique constraint (or index) to child table.
Let’s imagine we have two tables Users and UserProfiles:
CREATE TABLE "UserProfiles" ( "UProfileID" SERIAL NOT NULL, ... PRIMARY KEY("UProfileID") ); CREATE TABLE "Users" ( "UID" SERIAL NOT NULL, "UProfileID" int4 DEFAULT 0, ... PRIMARY KEY("UID"), UNIQUE("UProfileID"), FOREIGN KEY ("UProfileID") REFERENCES "Users"("UProfileID") ); |
That’s all. Opposite case where referenced and referencing table are changed did the trick either.
That is an interesting idea — you created a unique index on both tables, then used REFERENCES to see they remain linked.
LikeLike
Isn’t your foreign key supposed to be referencing the UserProfiles table?
LikeLike
Sorry, didn’t get it 🙂
LikeLike
Sold! Searched this cause I was thinking about it today and found this. Great! Thanks!
LikeLike
That’s 1-(0|1). Pretty sure that true 1-1 is impossible?
LikeLike