In a table, columns can typically accept NULL values by default. However, if you want to ensure that a particular column does not contain NULL values, you need to add the NOT NULL constraint/condition on that column.
The SQL NOT NULL Constraint
The NOT NULL constraint in SQL is used to ensure that a column in a table doesn’t contain NULL (empty) values, and prevent any attempts to insert or update rows with NULL values.
Usually, if we don’t provide value to a particular column while inserting data into a table, by default it is considered as a NULL value. But, if we add the NOT NULL constraint on a column, it will enforce that a value must be provided for that column during the data insertion, and attempting to insert a NULL value will result in a constraint violation error.
Syntax
Following is the basic syntax of NOT NULL constraint while creating a table −
CREATETABLE table_name ( column1 datatype NOTNULL, column2 datatype, column3 datatype NOTNULL,...);
Creating NOT NULL Constraint On a Table
To add the NOT NULL constraint on a column of a table, we just need to add the keyword “NOT NULL” after the column’s data type in the column definition.
Example
First of all, let us create a table named CUSTOMERS using the following query −
CREATETABLE CUSTOMERS( ID INTNOTNULL, NAME VARCHAR(20)NOTNULL, AGE INTNOTNULL, ADDRESS CHAR(25), SALARY DECIMAL(20,2),PRIMARYKEY(ID));
Let’s insert some values into the above created table using the following INSERT query −
INSERTINTO CUSTOMERS VALUES(1,'Ramesh','32','Ahmedabad',2000),(2,'Khilan','25','Delhi',1500),(3,'Kaushik','23','Kota',2500),(4,'Chaitali','25','Mumbai',6500),(5,'Hardik','27','Bhopal',8500),(6,'Komal','22','Hyderabad',9000),(7,'Muffy','24','Indore',5500);
The table will be created as shown below −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Verification
To display the structure of a table in MySQL database, we use the DESCRIBE command. The DESCRIBE command provides a summary of the columns, data types, and various attributes of the table as shown below −
DESCRIBE CUSTOMERS;
As we can see in the output below, the table shows information about the column names of the table, their types, and whether they are nullable or not.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | PRI | NULL | |
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
SALARY | decimal(20,2) | YES | NULL |
Removing a NOT NULL Constraint From the Table
In SQL, to remove a NOT NULL constraint of a column in an existing table, we need to use the ALTER TABLE statement. Using this statement, we can modify the definition of a column i,e you can change the name, data type or constraint of an existing column.
One of a way to remove the NOT NULL constraint on a column is to changing it to NULL.
Syntax
Following is the syntax to remove a not null constraint from the table in MySQL database −
ALTERTABLE table_name MODIFYCOLUMN column_name datatype NULL;
Were,
- table_name is the name of the table that contains the columns we want to modify.
- column_name is the name of the column that has the NOT NULL constraint you want to remove.
- datatype is the data type of the column.
Example
Following is the query to modify the constraint on the NAME column of the CUSTOMERS table to NULL in MySQL database −
ALTERTABLE CUSTOMERS MODIFYCOLUMN NAME VARCHAR(20)NULL;
Output
On executing the above query, the output is displayed as follows −
Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Now, let us display the structure of the table named “CUSTOMERS” using the following query −
DESCRIBE CUSTOMERS;
As we can see in the table below, the column “NAME” is modified to nullable, which means NULL values are allowed in this column.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | PRI | NULL | |
NAME | varchar(20) | YES | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
SALARY | decimal(20,2) | YES | NULL |
Adding a NOT NULL Constraint to the Existing Table
In the previous section, we have removed the NOT NULL constraint on a column by changing its definition using the ALTER TABLE statement. Similarly, we can add a NOT NULL constraint to a column in an existing table using the ALTER TABLE statement.
Syntax
Following is the SQL syntax to add the NOT NULL constraint to the existing column in MySQL database −
ALTERTABLE table_name MODIFYCOLUMN column_name datatype NOTNULL;
Example
Assume the previously created table CUSTOMERS and let us modify the ADDRESS column ensuring that it does not allow null values using the following query −
ALTERTABLE CUSTOMERS MODIFYCOLUMN ADDRESS CHAR(25)NOTNULL;
Output
When we execute the above query, the output is obtained as follows −
Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
We can display the structure of the CUSTOMERS table using the following query −
DESCRIBE CUSTOMERS;
As we can see in the output below, the column “ADDRESS” is modified, which means NULL values are NOT allowed in this column.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | PRI | NULL | |
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | NO | NULL | ||
SALARY | decimal(20,2) | YES | NULL |
Leave a Reply