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

Question

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?

Answer 1

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.