Jump to content
Larry Ullman's Book Forums

Ch 5 Example 'Users' Table "Not Null" Entry Shows "Null"


Recommended Posts

Dear Masters,

 

I am creating the first table columns in mysql client following the instructions in Ch 5 page 125.

 

I have added the first_name and last_name columns with attribute "NOT NULL" but the "SHOW COLUMNS from users" shows the default values as "NULL".

My codes are:

 

CREATE TABLE users (

user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

first_name VARCHAR(20) NOT NULL,

last_name VARCHAR(40) NOT NULL);

 

The book says that mysql will change the user_id column to NULL for better performance. It does not say anything about the other columns and the books result image show that only the user_id has default value of NULL and others have blank values under the Default column.

 

Can any one please tell me what's the problem here.

Much appreciate.

Thanks

Placid

Link to comment
Share on other sites

First of, an auto_incremented integer cannot be null. If you try assigning that value, the next available unsigned integer will be stored instead. Null is actually a data type itself, (as a representation for "nothing" also must exist) so it does not make sense as a value in itself. Increasing the integer is MySQL's chosen implementation.

 

As for the names, that has a data type of "String". (Or Varchar more specifically) An empty value here is therefor "" (an empty String) and not null. I'm guessing you are using PHP to add content to this table? All data submitted using PHP has the standard value of an empty string. That is therefor completely valid values to insert. The most basic, and therefor also the "default value" for a String type, is therefor also an empty String as it's the least amount of info accepted. If you try to explicitly add null directly into a query (using something like phpMyAdmin) then MySQL will complain about illegal values.

 

As for solutions, leave user_id be. That's standard functionality you'll enjoy it's there once you get familiar with it. As for the names, you need to add some validation to your input before you save it to the database. Simple functions for this include strlen(), !empty(), or even isset(). You'll find more on that in the book.

Link to comment
Share on other sites

First of all, when you create an AUTO_INCREMENT PRIMARY KEY NOT NULL column, MySQL will show NULL as the default value for it. This is correct. By having a default value (even of NULL), you don't have to provide a value for that column. And if you don't provide a value for that column, MySQL will use the default value of NULL. This will trigger the auto incrementation. So it may seem a little strange, but that is correct. 

 

Second, as for why the other columns would show a default value of NULL, when they are declared as NOT NULL, is a mystery to me. What happens if you insert a record without providing values for those columns?

Link to comment
Share on other sites

Dear Larry Sir,

 

Since I am new and I don't want to give you any wrong picture, I want to go into details.

 

I have deleted and recreated the 'users' table as follows:

CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(40) NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (user_id));

 

In phpMyAdmin, the 'SHOW COLUMNS FROM users' command shows the following table:

 

Field                            Type                                  Null     Key       Default     Extra

user_id                         mediumint(8) unsigned      NO       PRI        NULL        auto_increment

first_name                    varchar(20)                        NO                    NULL

last_name                    varchar(40)                        NO                    NULL

email                            varchar(60)                        NO                    NULL

pass                             char(40)                             NO                    NULL

registration_date          datetime                             NO                    NULL

 

Then, I tried to insert a new record without 'last_name' by the following code (as per second syntactical example on page 128 which says every column must be provided a value, even if it's NULL):

INSERT INTO users VALUES (NULL, 'Placid', NULL, 'placid@example.com', SHA1('nothing'), NOW())

 

The phpMyAdmin shows: #1048 - Column 'last_name' cannot be null

 

Then I tried the first method on Page 128 and inserted a record using the following code:

INSERT INTO users (first_name, email, pass, registration_date)
VALUES ('Placid', 'placid@example.com', SHA1('nothing'), NOW());

 

This time, the insert was successful. And the 'SELECT * FROM users' shows all the fields keeping the last_name field as blank.

 

I hope I am clear.

 

Finally, I thank you for your reply. I have grown huge respect for you reading your book. I am really learning. Thanks again.

 

Placid

Link to comment
Share on other sites

The results you are getting are correct. There are 2 syntaxes that can be used to insert a record:

  1. provide the column names and column values
  2. provide the column values only.

Using the latter syntax requires that you provide a value for every column. If a value is not provided, then the default will be used. If no default has been defined, NULL will be used. If NULL is not allowed as in the case of last_name, you will get an error.

 

I agree that it is misleading that the mysql SHOW COLUMNS command displays the default value as NULL. Look at it using phpmyadmin browse and you'll see that the default value is displayed as none.

Link to comment
Share on other sites

Dear margaux,

Thanks for your reply. Yes, the browse shows just as you suggested.

I have even tried the SHOW COLUMNS command in mysql client, and it shows the same misleading value (NULL) under the default column. Whatever, I think I can go on with it though.

Thanks again.

Placid

Link to comment
Share on other sites

 Share

×
×
  • Create New...