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

Postgresql – How to Create a Conditional Unique Constraint Based on Latest Updates in SQL

I have a log table that tracks updates, structured like this:

CREATE TABLE user_log (
    name_id VARCHAR,
    modified DATE,
    -- Other fields...
);

I aim to implement a unique constraint based on the name_id, ensuring that only the most recent entry is displayed of each name_id. Subsequently, I plan to establish a foreign key constraint on another table, referencing the name_id attribute.

I have only could create a composite key, what is the correct methodology to solve this kind of problem?

Tags:

2

Answers


  1. 0

    SQL doesn’t have built-in support for constraints that enforce unique records based on aggregate functions like MAX. This makes it challenging to create a unique constraint on name_id to ensure that only the latest record is kept.

    One solution is to create a separate table that stores just the latest record for each name_id, with a unique constraint on name_id. To keep this table updated, you can use a trigger on the original user_log table to insert or update records in the "current" table whenever there’s a new log entry.

    Login or Signup to reply.
  2. 0

    You create a separate table containing the name_id values and put a unique index on it.
    If you want you may keep the latest modified value as well.

    You may update this table using triggers on the original table.

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

Back To Top
Search