Create a column on my table that allows null but is set by default to empty (not null)?

MySQLMySQLi Database

You need to use default keyword for this. The syntax is as follows −

alter table yourTableName add yourColumnName yourDataType NULL Default '';

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table AllowNullDefaulNotNullDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> UserName varchar(20),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.65 sec)

Let us add a new column that allow NULL value but default value set to NOT NULL. The query is as follows −

mysql> alter table AllowNullDefaulNotNullDemo add UserAddress varchar(20) NULL Default'';
Query OK, 0 rows affected (1.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now check the description of table using DESC command. The query is as follows −

mysql> desc AllowNullDefaulNotNullDemo;

The following is the output −

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| UserName    | varchar(20) | YES  |     | NULL    |                |
| UserAddress | varchar(20) | YES  |     |         |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.17 sec)

Now you can insert some records in the table using insert command. In the below query the column UserAddress allow NULL value and default set value to NOT NULL.

Let us insert records in this column. The query is as follows −

mysql> insert into AllowNullDefaulNotNullDemo(UserAddress) values('US');
Query OK, 1 row affected (0.14 sec)
mysql> insert into AllowNullDefaulNotNullDemo(UserAddress) values(NULL);
Query OK, 1 row affected (0.16 sec)
mysql> insert into AllowNullDefaulNotNullDemo() values();
Query OK, 1 row affected (0.12 sec)

Check specific records of the newly added column i.e. UserAddress. The query is as follows −

mysql> select UserAddress from AllowNullDefaulNotNullDemo;

The following is the output −

+-------------+
| UserAddress |
+-------------+
| US          |
| NULL        |
|             |
+-------------+
3 rows in set (0.00 sec)
raja
Published on 27-Feb-2019 11:45:55
Advertisements