PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 1 to 1 relation postgres

I tried to create a 1-to-1 relationship in PostgreSQL but when i check the ERD it looks like a 1-to-many relationship.

this is my SQL script:

CREATE TABLE capital (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);

CREATE TABLE country (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100), 
  capital_id INTEGER, 
  CONSTRAINT fk_capital FOREIGN KEY(capital_id) REFERENCES capital(id)
);

enter image description here

Does postgres don’t support 1-to-1?

thanks in advance,

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @Schwern here is used country and capital just as an example, when running this script:

    CREATE TABLE capital (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);

CREATE TABLE country (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100), 
  capital_id INTEGER UNIQUE, 
  CONSTRAINT fk_capital FOREIGN KEY(capital_id) REFERENCES capital(id)
);

    I used pgAdmin and DbBeaver to visualize the database both show it as an one-to-many relationship, it seems like both tools have problem with visualizing one-to-one relationships but if you have the column marked as unique and added the constraint it should be a one-to-one since it can only exists on one record.


  2. 0

    PostgreSQL supports 1-to-1, but pgAdmin is not PostgreSQL.

    As others have pointed out in the comments, your relationship is 1-to-many. You’re missing the unique constraint necessary to make it 1-to-1.

    pgAdmin may not support 1-to-1 relationships. The documentation does not have a way to make a 1-to-1 relationship.

    You can try other visualization tools and see if they work better.

    I find 1-to-1 relationships indicative of a design problem. In your example, the country and its capital are two different entities, but they don’t necessarily need two different tables. You can store both entities in one table.

    Or we can go the other way. A capital is just a special city. What if you want to store more cities? You need a cities table.

    create table cities (
  id serial primary key,
  name text not null,
  country_id integer not null references countries,
  capital boolean default false
)

    Now it’s 1-to-many.

    And to enforce one country, one capital…

    create unique index one_capital_constraint on cities(country_id) where capital

    Instead of a 1-to-1 relationship, we have a more useful 1-to-many and a constraint.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search