I've created a table in mySQL like this:
CREATE TABLE IF NOT EXISTS `LibraryManager`.`Card` (
`card_id` INT NOT NULL AUTO_INCREMENT,
`card_registerDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`card_expiredDate` TIMESTAMP,
PRIMARY KEY (`card_id`))
ENGINE = InnoDB;
card_expiredDate
card_registerDate
Setting the default value for expiry column using create statement is not possible as such, instead use trigger. For that you will have to slightly modify you create statement. Just change the datatype of expiry column, your new query:
CREATE TABLE IF NOT EXISTS `LibraryManager`.`Card` (
`card_id` INT NOT NULL AUTO_INCREMENT,
`card_registerDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`card_expiredDate` DATETIME,
PRIMARY KEY (`card_id`))
ENGINE = InnoDB;
and then fire the following trigger
CREATE TRIGGER before_insert_library_card
BEFORE INSERT ON `LibraryManager`.`Card`
FOR EACH ROW
SET new.card_expiredDate = adddate(CURRENT_TIMESTAMP,30);
hope this helps.
P.S: Triggers slow down your insert operations(or whatever operation they are applied before/after). I suggest you set these values programmatically using php/python or whatever the backend you are using.
Related: