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

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.

About these ads

Nerd.

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

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 73 other followers

%d bloggers like this: