PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to make sure on update that 2 columns belong to each other in a different table?

I have 2 tables, referenced through a FK.

users table (id as PK)

cars table (id as PK, userId as FK to users, unique relationship id-userId)

On a third table, I want to record some data from cars.but in case a car is not yet registered, the carId is null.

question: when I update the third table, how can I make sure the car belongs to that user? if I use a trigger on insert for validation, how to make sure the data remains consistent when the cars table is updated?

I add a diagram, to be more explicit.

enter image description here

Tags:

2

Answers


  1. 0

    You can do this check trough a trigger before UPDATE.

    CREATE OR REPLACE FUNCTION ftrig_before_update_contributions() RETURNS trigger AS $BODY$
BEGIN
    IF NEW.carsId NOT IN (SELECT id FROM cars WHERE userId = NEW.userId) THEN 
      RETURN OLD;  --or you could: RAISE EXCEPTION 'This car does not belong to the user!';   
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trig_before_update_contributions BEFORE UPDATE ON contributions FOR EACH ROW EXECUTE PROCEDURE ftrig_before_update_contributions();

    LATER EDIT:

    As for the second part of your question, "how to make sure the data remains consistent when the cars table is updated" you can do this also via a trigger after update on the cars table:

    CREATE OR REPLACE FUNCTION ftrig_after_update_cars() RETURNS trigger AS $BODY$
BEGIN
    IF (NEW.userId <> OLD.userId) AND (NEW.id = OLD.id) THEN 
      UPDATE contributions SET userId = NEW.userId WHERE (userId = OLD.userId) AND (carsId = NEW.id);
    ELSE -- handle change on cars primary key.
        IF (NEW.userId <> OLD.userId) AND (NEW.id <> OLD.id) THEN 
            UPDATE contributions SET userId = NEW.userId, carsId = NEW.id WHERE (userId = OLD.userId) AND (carsId = OLD.id);
        ELSE
            IF NEW.id <> OLD.id THEN
                UPDATE contributions SET carsId = NEW.id WHERE carsId = OLD.id;
            END IF;
        END IF;
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trig_after_update_cars after UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE ftrig_after_update_cars();

    If you have ON UPDATE CASCADE on the carsId foreign key definition of the contribution table then the trigger is more simple:

    CREATE OR REPLACE FUNCTION ftrig_after_update_cars() RETURNS trigger AS $BODY$
BEGIN
    IF NEW.userId <> OLD.userId THEN 
      UPDATE contributions SET userId = NEW.userId WHERE (userId = OLD.userId) AND (carsId = NEW.id);
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trig_after_update_cars after UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE ftrig_after_update_cars();
    Login or Signup to reply.
  2. 0

    The only good solution is a foreign key constraint. Create a (redundand) unique constraint on cars("userId", id) and reference that in the foreign key on contributions.

    As long as contributiobs."carId" is NULL, the foreign key won’t be enforced.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search