How to add not null constraint to existing column in MySQL

by

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.

More Related Contents:

  1. add column to mysql table if it does not exist
  2. MySQL: ALTER TABLE if column not exists
  3. How do I add a custom CHECK constraint on a MySQL table?
  4. Unique constraint that allows empty values in MySQL
  5. Adding multiple columns AFTER a specific column in MySQL
  6. How can I modify the size of column in a MySQL table?
  7. How to truncate a foreign key constrained table?
  8. Error renaming a column in MySQL
  9. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  10. A constraint to prevent the insert of an empty string in MySQL
  11. ALTER TABLE to add a composite primary key
  12. MySQL Removing Some Foreign keys
  13. Alter MySQL table to add comments on columns
  14. Create an index on a huge MySQL production table without table locking
  15. MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
  16. Use Composite Primary Key as Foreign Key
  17. ALTER table – adding AUTOINCREMENT in MySQL
  18. How to remove constraints from my MySQL table?
  19. How to change MySQL column definition?
  20. Alter table if exists or create if doesn’t
  21. MySQL distinction between e and é (e acute) – UNIQUE index
  22. MySQL and Check Constraints
  23. List of Constraints from MySQL Database
  24. `MODIFY COLUMN` vs `CHANGE COLUMN`
  25. How to do unique constraint works with NULL value in MySQL
  26. How do I add more members to my ENUM-type column in MySQL?
  27. Optimizing MySQL for ALTER TABLE of InnoDB
  28. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)
  29. Troubleshooting “Illegal mix of collations” error in mysql
  30. How to remove leading and trailing whitespace in a MySQL field?

Leave a Comment