Create relation between two PostgreSQL tables

Question

This is structure of two existing tables:

Table: Location

  -------------------------
  PK varchar | Symbol               
  PK int     | LocationID
  FK int     | Shop 
  -  varchar | Address

Information:

Composite key of:               Symbol and LocationID
Foreign key to Shop's table:    Shop

Table: Shop

  -------------------------
  PK varchar | Symbol           
  PK int     | ShopID
  timestamp  | SomeDate

Information:

Composite key of: Symbol and ShopID

This is how i can join both tables:

SELECT * FROM Location As location 
         INNER JOIN Shop As shop
         ON location.Shop = Shop.ShopID AND location.Symbol = Shop.Symbol

However in Postgres I need to make relation between those tables. In DbEaver in the ER diagram there is no graphical possibility to draw relationship.

What is the command to create such relationship?

CREATE TABLE statements are usually a lot better if you want to show us the structure of your table. Your "format" is highly unusual - a CREATE TABLE statement would be much easier to understand — user330315
– user330315, Commented Jul 22, 2019 at 13:32

user330315 · Accepted Answer · 2019-07-22 13:29:33Z

4

As documented in the manual you declare a foreign key constraint:

alter table location
  add constraint fk_location_shop
  foreign key (shop, symbol) 
  references shop (shopid, symbol);

The columns of the foreign key must include all columns of the primary key of the referenced table. That's why you need foreign key (shop, symbol)

answered Jul 22, 2019 at 13:29

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Henry Over a year ago

can you also tell me when there is 1-1 relatshion how to make such command?

Collectives™ on Stack Overflow

Create relation between two PostgreSQL tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related