What's the difference or benefit of writing "NOT NULL" in a mysql field creation...
For example if I'm creating a table like this...
CREATE TABLE IF NOT EXISTS game(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
description VARCHAR(200) NOT NULL,
PRIMARY KEY(id)
)
Here, id and name always has a value so their NOT NULL is just fine. I get that. But, description is an optional field so, it can be blank.
So, in this situation, should I put NOT NULL or not ?
NULL and a blank field are not the same thing (unless, under some circumstances, you're a brain-dead DBMS coughOraclecough).
NULL means unknown or not applicable whereas a blank field means, well, known and blank.
It depends entirely on how you want to handle the field itself. For example, let's consider the middle initial of your name. If you do not wish to distinguish between 'unknown' and 'does not have one', set the column as NOT NULL and just use a blank value to represent both - that will ease your queries somewhat.
However, if you want to mail all your clients without middle names (for whatever bizarre reason) without bothering those where you don't know if they have one, you need to be able to distinguish between the two classes.
select first || ' ' || last as full from table where middle is null or middle = '' union all select first || ' ' || middle || ' ' || last as full from table where middle <> ''. Slightly easier if column is not nullable.concat_ws will still need the union unless you want multiple consecutive separators for those without a middle name, yes?create table x (a varchar(10), b varchar(10), c varchar(10)); insert into x values ('pax', NULL, 'diablo'); insert into x values ('george', 'w', 'bush'); select concat_ws('@',a,b,c) from x; There, now isn't that better.First of all, in your situation, you really don't have to put NOT NULL, if you put NOT NULL， when you insert data to your database , if the field used NOT NULL, its mandatory to put values to this field, however ,as mentioned earlier by paxdiablo, that NULL doesn't mean blank field, since blank field can be of a lot of spaces or one space. Anyways, if it is not mandatory to have values in a certain Field, you really don't have to put NOT NULL.( I really don't think it has anything to do with braindead DBMS or not @paxdiablo)
'' and NULL for some data types. It was not central to the discussion, I just can't pass up the opportunity for some Larry-bashing :-)what my opinion is, NOT NULL is for those field that with dependency such as primary key, foreign key.
what i working previously, in a function(a,b,c,d,e); without enter foreign key value, it execute but return no result and the auto_increment value is increased. this is not a good practice. if with NOT NULL, the insertion will be stopped when foreign key value is NULL, the auto_increment value wont be increase.
to handle parent record deletion, there is a cascade action or you can make it manually update another value to the record.
statusand it could be either0 1and your app rely on0,1and no null values to see the status of certain things.