How to make a table column an average of two other columns

Question

Is there a way that I could make the column type an equation of two other columns? Here is my table

CREATE TABLE `tbl_leaderboard` (
    `leaderboardID` INTEGER NOT NULL AUTO_INCREMENT,
    `totalScore` INTEGER DEFAULT 0 NOT NULL,
    `gamesPlayed` INTEGER DEFAULT 0 NOT NULL,
    `averageScore` INTEGER DEFAULT 0 NOT NULL,
    `player_score` INTEGER DEFAULT 0 NOT NULL,
    `userID` INTEGER NOT NULL,
    `playerID` INTEGER NOT NULL,
    PRIMARY KEY (`leaderboardID`),
    FOREIGN KEY (`userID`) REFERENCES `tbl_user`(`userID`)  ON DELETE CASCADE,
    FOREIGN KEY (`playerID`) REFERENCES `tbl_player`(`playerID`) ON DELETE CASCADE
);

I would like averageScore to equal totalscore/gamesPlayed. Is it possible to make the column type work like this, or does it only work on the insert statement?

Answer 1

it's called computed column or generated column feature:

CREATE TABLE `tbl_leaderboard` (
    `leaderboardID` INTEGER NOT NULL AUTO_INCREMENT,
    `totalScore` INTEGER DEFAULT 0 NOT NULL,
    `gamesPlayed` INTEGER DEFAULT 0 NOT NULL,
    `averageScore` DOUBLE as (totalscore /gamesPlayed) ,
    `player_score` INTEGER DEFAULT 0 NOT NULL,
    `userID` INTEGER NOT NULL,
    `playerID` INTEGER NOT NULL,
    PRIMARY KEY (`leaderboardID`),
    FOREIGN KEY (`userID`) REFERENCES `tbl_user`(`userID`)  ON DELETE CASCADE,
    FOREIGN KEY (`playerID`) REFERENCES `tbl_player`(`playerID`) ON DELETE CASCADE
);

and you don;t have to mention it in your update/insert statements , since it automatically will be calculated based on other columns