CODEWITHSUNDEEP
sqlpostgresqlprimary-key
RobertW
RobertW

Reputation: 372

PostgreSQL: How to link 2 tables?

I have a table defined like this:

CREATE TABLE public.journeys (
  journey_id uuid NOT NULL UNIQUE DEFAULT uuid_generate_v4(),
  name text NOT NULL,
  user_id uuid NOT NULL,
  date_created timestamptz NOT NULL,
  date_deleted timestamptz NULL,
  route_id uuid NOT NULL,
  CONSTRAINT fk_users
    FOREIGN KEY(user_id)
      REFERENCES users(user_id)
);

What I want to do now is create a second table that will connect to this table above. Here's its definition:

CREATE TABLE public.routes (
  route_id uuid NOT NULL UNIQUE DEFAULT uuid_generate_v4(),
  idx smallint NOT NULL,
  date timestamptz NULL,
  latitude real NOT NULL,
  longitude real NOT NULL,  
  CONSTRAINT route_key 
    PRIMARY KEY (route_id, idx),
  CONSTRAINT fk_journeys
    FOREIGN KEY(route_id)
      REFERENCES journeys(route_id)
);

The notion is that for every Journey there will be a connected Route that simply consists of a series of Latitude, Longitude points. So for a given route_id in journeys there will be N records in routes. Every record in a given route will share the same route_id but each one will have a unique idx (ie. 0, 1, 2, ...).

This is the error I'm getting when I try creating public.routes:

SQL Error [42830]: ERROR: there is no unique constraint matching given keys for referenced table "journeys"

What am I doing wrong and how do I fix this?

Robert

Upvotes: 0

Views: 466

Answers (1)

RobertW
RobertW

Reputation: 372

I read several more threads on the subject and then realized that that journeys.route_id was not being declared as UNIQUE.

So within the public.journeys declaration, this fixed the problem:

route_id uuid NOT NULL UNIQUE,

Upvotes: 1

Related Questions