You've learned to use constraints to set a default timestamp. But what if we wanted an "updatedAt" field that reflected when the last change was?

Do we need to do it in our code when we make updates to the row in the table? Not at all! There's a fourth part to our fields besides the name, type, and constraints. It's called a 'clause', and it's like a special instruction that we can attach to certain data types to define additional behavior or functionality.

Clauses allow us to automate things in our database based on certain actions.

For instance, if we add an "ON UPDATE" clause to a new field called 'updatedAt' in our table:

updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

The 'ON UPDATE CURRENT_TIMESTAMP' is our 'Clause'

It means "On every update to this entry- set this value to 'CURRENT_TIMESTAMP'"

Putting it all together we have this:

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

And we can now begin inserting some data!

0 Comments

Top comments

"Please login to view comments"

Join the Conversation!

Subscribing gives you access to the comments so you can share your ideas, ask questions, and connect with others.

Upgrade your account