MySQL issue with altering column to add default

Question

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

jpw · Accepted Answer · 2015-07-11 00:23:20Z

1

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.

edited Jul 11, 2015 at 0:23

answered Jul 10, 2015 at 23:57

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Millenial2020 Over a year ago

i get an error that says: Invalid default value for 'created'

jpw Over a year ago

@user3862830 I'm guessing you are maybe using a MySQL server version < 5.6; If so try changing the datetime to timestamp as earlier versions didn't initialize datetime fields.

Collectives™ on Stack Overflow

MySQL issue with altering column to add default

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related