MySQL? help!?

Question

Can somebody give me a general justification for the use of NOT NULL attribute on fields in MySQL???

Answer 1

In SQL NULL represents the the absence of a value. In MySQL an explicit NULL may also represent the next value of a pseudo-sequence and an implicit NULL may represent an implicit default value (a zero or empty string) determined by MySQL.



Example 1. When NULL is not NULL



CREATE TABLE null_1 (

id INT NOT NULL,

text1 VARCHAR(32) NOT NULL,

text2 VARCHAR(32) NOT NULL DEFAULT 'foo'

);



INSERT INTO null_1 (id) VALUES(1);

INSERT INTO null_1 (text1) VALUES('test');



mysql> SELECT * FROM null_1;

+----+-------+-------+

| id | text1 | text2 |

+----+-------+-------+

| 1 | | foo |

| 0 | test | foo |

+----+-------+-------+

2 rows in set (0.00 sec)



Here MySQL has inserted an empty string into column text1 on the first row, and zero into column id on the second row, even though each column is defined as NOT NULL with no default value. As no value was provided in the INSERT statements, these can be considered an attempt to insert implicit NULLs into theses columns, which should normally cause the statements to fail.





... If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: ...

Answer 2

Not Null applied in primary key:

If a Not Null property is used in field as primary key then when you're adding records, this field can not be left blank. It must always contain a value.



Not Null in a query:

Let's say that you had a table for customer's orders. And in that table, there's a field called packing slip. If a record (an order) has a packing slip number/value, it means that the order has been received; otherwise, it's null.



So, if you're trying to run a report of all the orders that have been received, then you would put Not Null on the packing slip field as your criteria when you write a query.



Hope that helps clarify it a bit.

Answer 3

Usually Not Null would be used when using that field as primary key... example a part number.. you wouldn't want a blank part number...So using not null will require that field for records to be added.

Answer 4

NOT NULL basically means that this certain field cannot be null and must be filled in by the user.

Answer 5

You want to use not null when a value is required. Keys for one should probably never be null.

Answer 6

NOT NULL means that particular field should have a value and cannot be make non.

Answer 7

CREATE TABLE tbl_sample (

pgid int(11) NOT NULL default '0',

engtitle mediumtext NOT NULL,

) TYPE=MyISAM;

Answer 8

required fields.



For instance, a primary key on a table that will be referenced.

Answer 9

get a dedicated server where you can control the firewall so you can connect remotely like i hv ;)