Search code examples
mysqlalter

MySQL issue with altering column to add default

I have a table named Users with a column call created. Whenever a record is created I want to add the datetime.

Users Table:

CREATE TABLE `Users` (
  `userId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fullName` varchar(50) DEFAULT NULL,
  `firstName` varchar(25) NOT NULL DEFAULT '',
  `lastName` varchar(25) NOT NULL DEFAULT '',
  `address` varchar(50) NOT NULL DEFAULT '',
  `city` varchar(25) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `zipCode` varchar(25) DEFAULT NULL,
  `email` varchar(50) NOT NULL DEFAULT '',
  `cellPhone` varchar(15) DEFAULT NULL,
  `birthDate` date NOT NULL,
  `creditCard` varchar(250) NOT NULL DEFAULT '',
  `subscriptionStarted` date NOT NULL,
  `subscriptionEnded` date NOT NULL,
  `basicPlan` tinyint(1) DEFAULT NULL,
  `standardPlan` tinyint(1) DEFAULT NULL,
  `premiumPlan` tinyint(1) DEFAULT NULL,
  `staff` tinyint(1) DEFAULT NULL,
  `admin` tinyint(1) DEFAULT NULL,
  `systemAdmin` tinyint(1) DEFAULT NULL,
  `edited` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

now i added this extra query to make my created field get the current datetime when a new record is created. 

ALTER TABLE Users
ALTER COLUMN created SET DEFAULT CURRENT_TIMESTAMP

The problem is that I get the following error when running the alter table query

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP' at line 2

Solution

  • Your syntax is slightly off, I think you have to specify the column to change:

    ALTER TABLE Users CHANGE created created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

    See this sample SQL Fiddle for an example.