CODEWITHSUNDEEP
sqlpostgresqldatabase-designforeign-keysprimary-key
Federico Gentile
Federico Gentile

Reputation: 5940

How to reference a SQL table if primary key has more than one column?

I am learning postgresql and I have created 2 tables: goals and results. Each table has a primary key which is formed by 3 columns:

I did this so that each row must be unique not only by its id but also depending on when the goal or result is still valid. Here is my sql code:

CREATE TABLE goals (
  goal_id INT,
  goal_title VARCHAR(80),
  goal_description VARCHAR(300),
  goal_valid_from_date TIMESTAMP,
  goal_valid_until_date TIMESTAMP,
  goal_deleted_flag BOOLEAN,
  PRIMARY KEY (goal_id, goal_valid_from_date, goal_valid_until_date)
);

CREATE TABLE results (
  result_id INT,
  goal_id INT,
  result_description VARCHAR(300),
  result_target FLOAT,
  result_timestamp DATE,
  result_valid_from_date TIMESTAMP,
  result_valid_until_date TIMESTAMP,
  result_deleted_flag BOOLEAN,
  PRIMARY KEY (result_id, result_valid_from_date, result_valid_until_date)
);

My goal now is to create a foreign key so that I am able to connect the 2 tables based on the goal_id column only and not the valid_from/until_date columns otherwise they would never match.

I tried to achieve this by using the following lines of code:

ALTER TABLE results
ADD FOREIGN KEY(goal_id)
REFERENCES goals(goal_id) on delete set null;

However I get an error:

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

Would you be able to propose a smart and elegant way to achieve my goal?

Upvotes: 0

Views: 984

Answers (2)

mshabou
mshabou

Reputation: 592

it is very clear in the documentation

FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

you need to put the list of column_name

Upvotes: 2

SebDieBln
SebDieBln

Reputation: 3469

... so that I am able to connect the 2 tables based on the goal_id column only ...

As the error message already suggests, you need to add a matching UNIQUE CONSTRAINT:

ALTER TABLE goals
ADD CONSTRAINT u_goals_goal_id
UNIQUE (goal_id)

However, this constraint is more restrictive than your actual primary key, which might not be what you want, if your current design is on purpose and not by accident (see the comments of @MikeOrganek and @ErwinBrandstetter).

If it is what you want then you should consider making this your primary key instead.

For your exclusion problem (no two goals may have overlapping time periods) take a look at the example in the manual, it literally describes your case.

Upvotes: 2

Related Questions