INSERT or UPDATE Table with Foreign Key Constraints-CodePudding

I am looking for a solution that will allow me to insert, update or delete data effectively from tables with foreign key constraints.

These are my sample tables:

CREATE TABLE olympic.tb_register (
  athlete_id    CHARACTER(7) NOT NULL,
  round_number  INT NOT NULL,
  discipline_id INT NOT NULL,
  register_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  register_position INT DEFAULT NULL,
  register_time     TIME DEFAULT NULL,
  register_measure  REAL DEFAULT NULL,
  CONSTRAINT pk_register PRIMARY KEY (athlete_id, round_number, discipline_id),
  CONSTRAINT fk_register_athlete FOREIGN KEY (athlete_id) REFERENCES olympic.tb_athlete (athlete_id),
  CONSTRAINT fk_register_round FOREIGN KEY (discipline_id, round_number) REFERENCES olympic.tb_round (discipline_id, round_number)
);

CREATE TABLE olympic.tb_athlete    (
  athlete_id    CHARACTER(7) NOT NULL,
  name          CHARACTER VARYING(50) NOT NULL,
  country       CHARACTER VARYING(3) NOT NULL,
  substitute_id  CHARACTER(7) DEFAULT NULL,
  CONSTRAINT pk_athlete PRIMARY KEY (athlete_id),
  CONSTRAINT fk_athlete_substitute FOREIGN KEY (substitute_id) REFERENCES olympic.tb_athlete (athlete_id)
);

When I run the command:

BEGIN;
INSERT INTO tb_register  VALUES(7777777,7,7,'2022-06-02 00:00:00',NULL,NULL);
INSERT INTO  tb_athlete  VALUES(7777777,'xxxxxx','XXX','xxxxxxx');
DELETE FROM tb_register WHERE athlete_id = 1320573;
DELETE FROM tb_athlete WHERE athlete_id = 1320573;
COMMIT;

I receive the following error:

ERROR:  insert or update on table "tb_register" violates foreign key constraint "fk_register_athlete"
DETAIL:  Key (athlete_id)=(7777777) is not present in table "tb_athlete".
SQL state: 23503

Can anyone help me understand how to solve this?

Thank you (:

CodePudding user response：

The order of your create table and insert statements needs to be reversed. When olympic.tb_register comes to life it needs to already see olympic.tb_athlete for its reference constraints against that table to make sense - you can't force a table to make sure its rows match a not-yet-existing table.

It's the same situation for inserts: when a new row enters olympic.tb_register, it has to already see its corresponding row in olympic.tb_athlete. Otherwise it'll think someone is trying to register a non-existing athlete.

You can also use on delete cascade and on update cascade in your reference constraint so that you only need to issue a delete/update on tb_athlete, which will automatically delete/update the corresponding record in tb_register:

  CONSTRAINT fk_register_athlete 
     FOREIGN KEY (athlete_id) 
     REFERENCES olympic.tb_athlete (athlete_id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,