Syntax error How to add NOT NULL constraint to an already created MySQL column?

How to add NOT NULL constraint to an already created MySQL column?

Achieve this using ALTER TABLE. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(100)
   -> );
Query OK, 0 rows affected (0.86 sec)

Let us check the description of the table −

mysql> desc DemoTable;

This will produce the following output −

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| StudentId   | int(11)      | NO   | PRI | NULL    | auto_increment |
| StudentName | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Here is the query to add a NOT NULL constraint to the other column “StudentName”, which wasn’t set NOT NULL initially −

mysql> alter table DemoTable modify StudentName varchar(100) NOT NULL;
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the description of the table once again −

mysql> desc DemoTable;

This will produce the following output −

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| StudentId   | int(11)      | NO   | PRI | NULL    | auto_increment |
| StudentName | varchar(100) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:26+05:30

297 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements