- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to add not null constraint to existing column in MySQL?
To add not null constraint to an existing column in MySQL, we will use the ALTER command. This is a type of validation to restrict the user from entering null values.
Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table.
mysql> create table AddNotNUlldemo - > ( - > name varchar(100) - > ); Query OK, 0 rows affected (0.44 sec)
To insert records.
mysql> insert into AddNotNUlldemo values('John');
Query OK, 1 row affected (0.19 sec)
mysql> insert into AddNotNUlldemo values('Bob');
Query OK, 1 row affected (0.19 sec)
To display all the records.
mysql> select *from AddNotNUlldemo;
Here is the output.
+------+ | name | +------+ | John | | Bob | +------+ 2 rows in set (0.00 sec)
The following is the syntax to add a constraint to an existing column.
ALTER table yourTableName modify column_name data type constraint;
Let us now implement the above syntax to implement the below query. Here, we are including “not null” constraint.
mysql> ALTER table AddNotNUlldemo modify name varchar(100) not null; Query OK, 0 rows affected (1.14 sec) Records: 0 Duplicates: 0 Warnings: 0
Now, we cannot insert a null value into the table “AddNotNUlldemo” because we have set the constraint above as “not null”. If we try to add a null value, we will get an error. The following is an example.
mysql> INSERT into AddNotNUlldemo values(null); ERROR 1048 (23000): Column 'name' cannot be null
To display all the records.
mysql> SELECT *from AddNotNUlldemo;
Here is the output.
+------+ | name | +------+ | John | | Bob | +------+ 2 rows in set (0.00 sec)
2K+ Views
