MySQL Syntax Error with both CURDATE() and NOW()

I am converting a database from MS SQL Server to MySQL. I am trying to create my users table as follows:

CREATE TABLE TBL_users (
      MemberID int NOT NULL AUTO_INCREMENT,
      UserFirst nvarchar(50),
      UserLast nvarchar(50),
      UserAccess int DEFAULT 3,
      PCode nvarchar(50),
      UserEmail nvarchar(50),
      SMS nvarchar(50),
      Carrier nvarchar(50),
      EmailType nvarchar(50) DEFAULT 'HTML',
      Device nvarchar(50),
      UserGroup nvarchar(50),
      RegDate datetime NOT NULL DEFAULT CURDATE(),
      isStrongPassword bit (0) NOT NULL DEFAULT,
      isHashedPassword bit (0) NOT NULL DEFAULT,
      PRIMARY KEY (MemberID)
);

I keep getting an error message that says I have a syntax error between the 'RegDate' line and the 'isHashedPassword' line? I have substituted CURDATE() for NOW() and get the same error. Also, I have tried the 'timestamp' data type for 'RegDate' but I don't want the 'RegDate' field to change when I do an update on a record in the database.

I have also changed the order of 'DEFAULT' and 'NOT NULL' in those lines but nothing seems to work??

Any ideas?
kenjpeteAsked:
Who is Participating?
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
See here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html
The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
0
 
Dave BaldwinFixer of ProblemsCommented:
I believe that in MySQL, DEFAULT must be a single value and not a function.  CURDATE() and NOW() can be used in an SQL Query but not as a default 'value'.
0
 
kenjpeteAuthor Commented:
Ok that worked! If I change the 'RegDate' to a data type of 'timestamp' and set the default value to 'CURRENT_TIMESTAMP' I don't get any errors (I also had to remove the 'DEFAULT' from 'isStrongPassword' and 'isHashedPassword'?).

However, what concerns me now is, if a user updates their account will the RegDate value change as a result of that change to a timestamp data type?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Dave BaldwinFixer of ProblemsCommented:
I don't know for sure but I think the DEFAULT value is only used on an INSERT.  Apparently, it only 'updates' if you tell it to or do an UPDATE with a NULL value for that column.  See here: http://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html
0
 
Chris StanyonCommented:
if a user updates their account will the RegDate value change as a result of that change to a timestamp data type?

Nope - it's the DEFAULT value that's set, so you won't have a problems. Give it whirl and see :)

And in your original SQL, you didn't set a DEFAULT value for the password fields - DEFAULT on it's own means nothing...
0
 
kenjpeteAuthor Commented:
Thanks Chris, I noticed that I omitted 'false' in those lines too!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.