CODEWITHSUNDEEP
sqlpostgresql
freedomflyer
freedomflyer

Reputation: 2541

Deleting related rows from other tables without primary key

I need to delete the associated rows from the "assign" table when a crew member is deleted.

However, when I create the tables, I get the error:

ERROR:  there is no unique constraint matching given keys for referenced table "assign"

It seems as though I need distinct values in the "assign" table, but the whole point is that I want to delete all associated information when a crew member is removed. How can I do this?

create table flight(
  flight_num BIGSERIAL PRIMARY KEY,
  source_city varchar,
  dest_city varchar,
  dep_time int,
  arr_time int,
  airfare int,
  mileage int
);

create table crew (
  id BIGSERIAL PRIMARY KEY,
  name varchar,
  salary int,
  position varchar,
  seniority int,
  fly_hours int,
  mgrid int,
  FOREIGN KEY (id) REFERENCES assign(id) ON DELETE CASCADE
);

create table assign (
  id int, # refers to a crew member id, not a row id
  flight_num int
);

Upvotes: 0

Views: 199

Answers (1)

user2864740
user2864740

Reputation: 61865

The foreign keys should be on the M-M join table, as so:

create table flight(
  flight_num int PRIMARY KEY,
  -- etc
);

create table crew (
  crew_id int PRIMARY KEY,
  -- etc
);

create table assign (
  crew_id int,
  flight_num int,
  -- If the corresponding record in either of these FKs is deleted then
  -- this record will be deleted.
  FOREIGN KEY (crew_id) REFERENCES crew(crew_id) ON DELETE CASCADE,
  FOREIGN KEY (flight_num) REFERENCES flight(flight_num) ON DELETE CASCADE,
  -- M-M usually should have a PK over both foreign columns, arranged in order
  -- of expected usage. A reverse covering index can be added if needed.
  PRIMARY KEY (flight_num, crew_id)
);

In this case the FKs are allowed because they are over a unique columns (PKs of crew and flight). The original error is because there was no unique contraint on the FK target (assign.crew_id).

Upvotes: 3

Related Questions