I have got a field that has been setup to allow for Null, but am now wanting to change that to not allow for Null.
When ever I try to change this I can't because PhpMyAdmin has set all the previous records to Null.
How can I fix this problem?
Is there a query I could put that will remove all the Null values and set the field to Non Null?
Of course. But what do you you mean by "remove all the NULL values?" Does that mean to delete all the rows that have a NULL in that field? If so, you could do this:
DELETE FROM your_table WHERE your_field IS NULL;
If you want to keep those rows, you'll have to put some other value in that field. Is there one that makes sense? If so, you could do this:
UPDATE your_table SET your_field = 42 WHERE your_field IS NULL;
(Where, of course, 42 is whatever value you want to use instead.)
If you want to keep the rows, and there isn't any other value you could use, then the NULLs that are there now should stay, and it might be time to reevaluate why you want to set the field to NOT NULL.
UPDATE my_precious_table
SET stinky_field TO my_favourite_non_null_value
WHERE stinky_field IS NULL;
You seem a bit confused as to what a null realy is. Here's an article I've published somewhere else that should be safe to republish here:
Null means there is no predefined value.
When a column is created in a table in a database there are some constraints that can be added. One of those constraints is wether or not the column allows null values.
A field on a row will be null if it is not explicitly set to something else. You can either supply a default value when the table is created, or you can set a value when adding a new row to the table, or update the row later on. To add or update rows in a table the INSERT and UPDATE commands are used.
When a field is null all comparisons to that field will also be null. This means that all boolean algebra misbehaves when a null value is involved: * True AND Null is Null * False AND Null is Null
and so on …
Ordinary mathematics doesn't work either since 5 + Null equals Null.
An interesting side effect of this is that when you compare two fields that are both null the result will not be true, it will be null.
To still be able to work with fields that can contain null values there are a couple of options: Both T-SQL (Microsoft SQL Server) and MySql has the functions COALESCE() and ISNULL().
ISNULL(): returns a predefined value if the value you are looking for is null. SELECT ISNULL(null, 2) will return 2
COALESCE(): returns the first value in a predefined list that is not null: SELECT COALESCE(a, b, c, d ...)
In MySql there is also a null safe comparison operator: a <=> b It will return 1 (true) if both values are null and 0 (false) if either value is null.
When it comes to counting rows in a table you also need to be aware of the implications of null. SELECT COUNT(*) FROM TableName will return the total amount of rows in the table, but SELECT COUNT(FieldName) FROM TableName will will only return the amount of rows where the column 'FieldName' is not null.
Quite often the database designer, more or less by lazyness, sets a default value for all columns in a table or constrains all feilds to not allow null. When the database designer does that everyone that uses the database is forced to make up 'magic' values when ever there is no actual value to use.
Suppose we have a table for storing vehicle information and in that table we have a column to store the fuel type of each stored vehicle. What fuel type should be used for a bicycle? If the column allows null the answer is easy; since there is no engine on a bicycle there is no fuel type to use, so just set the field to null on all rows representing bicycles. If the database designer instead had put the contraint on the column that null is not allowed the developer have to make up a value to represent 'no fuel type'. If the field is an integer zero might be used to do that. Furthermore, if there is a table that contains all viable fuel types that the vehicle table is supposed to reference to find names and descriptions for fuel types the developer have to put fake fuel types in the table so that the database management server can enforce the data integrity of the database.
Null is a very nice feature of databases that lots of 'ordinary' programming languages don't have, but a database developer and designer needs to keep track of where and how it is used.