Menu
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.
You can add a not null column at the time of table creation or you can use it for an existing table. In the above table, we have declared Id as int type that does not take NULL value. If you insert NULL value, you will get an error. Here is the query to add a not null column in an existing table using alter command.
To enforce NOT NULL for a column in MySQL, you use the ALTER TABLE .... MODIFY command and restate the column definition, adding the NOT NULL attribute.
The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL; The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
Just use an ALTER TABLE... MODIFY... query and add NOT NULL into your existing column definition. For example:
ALTER TABLE Person MODIFY P_Id INT(11) NOT NULL;
A word of caution: you need to specify the full column definition again when using a MODIFY query. If your column has, for example, a DEFAULT value, or a column comment, you need to specify it in the MODIFY statement along with the data type and the NOT NULL, or it will be lost. The safest practice to guard against such mishaps is to copy the column definition from the output of a SHOW CREATE TABLE YourTable query, modify it to include the NOT NULL constraint, and paste it into your ALTER TABLE... MODIFY... query.
Try this, you will know the difference between change and modify,
ALTER TABLE table_name CHANGE curr_column_name new_column_name new_column_datatype [constraints]
ALTER TABLE table_name MODIFY column_name new_column_datatype [constraints]
CHANGE.MODIFY. You cannot change the name of the column using this statement.Hope, I explained well in detail.
Would like to add:
After update, such as
ALTER TABLE table_name modify column_name tinyint(4) NOT NULL;
If you get
ERROR 1138 (22004): Invalid use of NULL value
Make sure you update the table first to have values in the related column (so it's not null)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With