0

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 ?

Tharindu Thisarasinghe
  • 3,846
  • 8
  • 39
  • 70
  • Think about a situation when you have a field called `status` and it could be either `0 1` and your app rely on `0,1` and no null values to see the status of certain things. – Abhik Chakraborty May 12 '15 at 08:31
  • It's up to you. To my way of thinking, if a description is unavailable (but could conceivably be provided) then it should be NULL. – Strawberry May 12 '15 at 08:37
  • putting NOT NULL means the field should not be null. If it is nullable then leave it as is and don't put NOT NULL or else it would prompt error once the field is null. – MAC May 12 '15 at 08:41

3 Answers3

4

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • What if you want to email all people using their full names, including middle names if they have them? – Strawberry May 12 '15 at 08:44
  • @Strawberry: assuming null allowed, something like `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. – paxdiablo May 12 '15 at 08:48
  • That's easier than CONCAT_WS() ?? I disagree – Strawberry May 12 '15 at 08:55
  • @Strawberry, that's why I said "something **like**" :-) In any case, `concat_ws` will still need the union unless you want multiple consecutive separators for those without a middle name, yes? – paxdiablo May 12 '15 at 08:58
  • @Stawberry: are you sure? Just tested (mysql5.6): `create table x (a varchar(10), b varchar(10), c varchar(10)); insert into x values ('pax', '', 'diablo'); insert into x values ('george', 'w', 'bush'); select concat_ws('@',a,b,c) from x;` and got `pax@@diablo, george@w@bush`. So I think you _do_ need the union. However, it _does_ work with NULL for `b`, so I'll just assume that's what you meant. But it's not really using NULL correctly. – paxdiablo May 12 '15 at 09:03
  • 1
    Consider the alternative: `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. – Strawberry May 12 '15 at 09:10
  • @Strawberry, yes, it's a shorter query but, as stated, it's not really using null properly. But I'm pragmatic mostly :-) – paxdiablo May 12 '15 at 09:45
  • (I think it *is* using NULL properly) Some people have middle names. Some people don't have middle names, and sometimes we just don't know. This IS NULL! – Strawberry May 12 '15 at 09:48
  • I'll make this the last comment since SO is complaining about the comment count :-) The reason I query its use of NULL is because NULL is supposed to mean unknown rather than known-and-blank. Hence only people where you don't _know_ if they have a middle name should have NULL. People _without_ a middle name should have an empty string there and it's those ones that will end up with consecutive separators in their full name. But I'd need to come up with a more sensible use case to make any further points so I'll let it lie :-) – paxdiablo May 12 '15 at 10:12
1

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)

driveyounuts
  • 21
  • 1
  • My comment re brain-deadedness of any particular DBMS had to do with the fact that it can't distinguish between `''` 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 :-) – paxdiablo May 12 '15 at 08:53
0

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.

Addition

to handle parent record deletion, there is a cascade action or you can make it manually update another value to the record.

Community
  • 1
  • 1
Yu Yenkan
  • 745
  • 1
  • 9
  • 32