How to add not null constraint to existing column in MySQL

Question

I have table name called  Person  with following column names   P Id int   LastName varchar   FirstName  varchar     I forgot to give NOT NULL Constraint to P Id   Now I tried with following query to add NOT NULL Constraint to existing column called P Id   1  ALTER TABLE  Person MODIFY   P Id NOT  NULL   2  ALTER TABLE Person ADD CONSTRAINT NOT  NULL NOT NULL  P Id     I am getting syntax error

User · Answer

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

User · Answer

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     You can change name and datatype of the particular column using CHANGE  You can modify the particular column datatype using MODIFY  You cannot change the name of the column using this statement    Hope  I explained well in detail

User · Answer

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

[mysql] How to add not null constraint to existing column in MySQL

Examples related to mysql

Examples related to constraints