How to add not null constraint to existing column in MySQL

mysql constraints
mymotherland picture mymotherland · Jun 10, 2011 · Viewed 210.1k times · Source

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

Answer

Shakti Singh picture Shakti Singh · Jun 10, 2011

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.

Related questions

How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More
MySQL Removing Some Foreign keys

I have a table whose primary key is used in several other tables and has several foreign keys to other tables. CREATE TABLE location ( locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ... ) ENGINE = InnoDB; CREATE TABLE assignment ( assignmentID INT NOT …

Read More
How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is: ALTER TABLE `tbl_magazine_issue` DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users` But I got an error: #1064 - You have an error in your SQL syntax; check the …

Read More