CODEWITHSUNDEEP
mysqlconstraintsalter-table
mymotherland
mymotherland

Reputation: 8226

How to add not null constraint to existing column in MySQL

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....

Upvotes: 217

Views: 282883

Answers (3)

David Thomas
David Thomas

Reputation: 4396

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)

Upvotes: 19

Maran Manisekar
Maran Manisekar

Reputation: 931

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.

Upvotes: 26

Shakti Singh
Shakti Singh

Reputation: 86386

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.

Upvotes: 343

Related Questions