Disallowing NULLs : Null « Data Type « SQL / MySQL






Disallowing NULLs

   
/*
mysql> DROP TABLE Employee;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Employee (
    ->     Name VARCHAR(50) NOT NULL,
    ->     Phone VARCHAR(15) NOT NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> Describe Employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(50) |      |     |         |       |
| Phone | varchar(15) |      |     |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO Employee (Name, Phone)
    ->             VALUES ('Joe Wang', '666 2323');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employee (Name) VALUES ('John Doe');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employee (Name, Phone) VALUES ('John Doe', NULL);
ERROR 1048 (23000): Column 'Phone' cannot be null
mysql> Select * from Employee;
+----------+----------+
| Name     | Phone    |
+----------+----------+
| Joe Wang  | 666 2323 |
| John Doe |          |
+----------+----------+
2 rows in set (0.00 sec)

*/  
DROP TABLE Employee;

CREATE TABLE Employee (
    Name VARCHAR(50) NOT NULL, 
    Phone VARCHAR(15) NOT NULL
);

Describe Employee;

INSERT INTO Employee (Name, Phone)
            VALUES ('Joe Wang', '666 2323');
INSERT INTO Employee (Name) VALUES ('John Doe');
INSERT INTO Employee (Name, Phone) VALUES ('John Doe', NULL);


Select * from Employee;

           
         
    
    
  








Related examples in the same category

1.Retrieve NULL value
2.Concatenate string with NULL value
3.Working with NULL Values
4.Use the IS NULL and IS NOT NULL operators
5.NULL means 'not having a value'
6.Retrieve NOT NULL value
7.List NOT NULL value and order it
8.Select NOT NULL value
9.IS NOT NULL in where clause
10.NULL value in where clause
11.Read NULL value
12.Dealing With NULL Data
13.An important one to note; the result is not 0 (false), it's NULL
14.To evaluate NULL rows
15.NULL is basically a third possible result of an evaluation: There's true, false, and then there's NULL.
16.Use the IS NULL (or IS NOT NULL) comparison instead
17.Assuming that NULL values sort ahead of all non-NULL values
18.Passing a NULL value to a function results in a NULL return value.
19.Set value to null
20.What is the result of the calculation 10 divided by 0?
21.If the value of an expression cannot be determined, the result is NULL: