Do you see a problem with this small piece of SQL?

**NightProwler** · Oct 3, 2012 14:28

The following code supposedly works. Unfortunately, it won't work for me. Do you see any error in this?

Code:

CREATE TABLE `admins` (
  `admin_id` int(11) NOT NULL auto_increment,
  `admin_username` varchar(40) NOT NULL default '',
  `admin_password` varchar(40) NOT NULL default '',
  `admin_status` varchar(40) NOT NULL default 'Offline',
  `admin_last_login` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`admin_id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `admins` VALUES (1, 'admin', 'admin', 'Offline', '');

(This is standard SQL or phpmyadmin, not PHP etc)

**guelphdad** · Oct 3, 2012 14:33

what doesn't work the table creation or the insert?
also, flaky in my opinion, you are creating column types that are NOT NULL but then make an empty value the default value. Why not just allow them to be NULLable then?

**rcashell** · Oct 3, 2012 23:22

You are using old syntax for the CREATE TABLE. The keyword TYPE has been deprecated and you should use ENGINE instead:

Code:

CREATE TABLE `admins` (
  `admin_id` int(11) NOT NULL auto_increment,
  `admin_username` varchar(40) NOT NULL default '',
  `admin_password` varchar(40) NOT NULL default '',
  `admin_status` varchar(40) NOT NULL default 'Offline',
  `admin_last_login` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`admin_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `admins` VALUES (1, 'admin', 'admin', 'Offline', '');

**K. Wolfe** · Oct 4, 2012 11:35

I believe you also want to use DEFAULT NULL, not ''. I believe this will actually cause a select of IS NULL to not return those rows, if queried.

**r937** · Oct 5, 2012 04:25

actually, for username and password, these should ~not~ be DEFAULT NULL

how much sense does it make to add an admin to the table with a NULL username and password????

**K. Wolfe** · Oct 8, 2012 07:29

Originally Posted by r937

actually, for username and password, these should ~not~ be DEFAULT NULL

how much sense does it make to add an admin to the table with a NULL username and password????

But why change up the design from normal columns if it should never be null. It doesn't make sense for it to be an empty string either.