1 to 1 relation postgres

Question

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

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

thanks in advance,

Answer 1

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.

Answer 2

@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.