1-to-1 relationship in PostgreSQL

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")

        "UProfileID" int4 DEFAULT 0,
        PRIMARY KEY("UID"),
        FOREIGN KEY ("UProfileID"REFERENCES "Users"("UProfileID")

That’s all. Opposite case where referenced and referencing table are changed did the trick either.


Tagged with: , , , ,
Posted in MicroOLAP Database Designer for PostgreSQL, PostgreSQL
4 comments on “1-to-1 relationship in PostgreSQL
  1. That is an interesting idea — you created a unique index on both tables, then used REFERENCES to see they remain linked.

  2. Chris Spotts says:

    Isn’t your foreign key supposed to be referencing the UserProfiles table?

  3. Devin Wilson says:

    Sold! Searched this cause I was thinking about it today and found this. Great! Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Map of wandering

Get every new post delivered to your Inbox.

Join 426 other followers

%d bloggers like this: