mysql约束类型 A P,如何在MySQL5.1中向现有列添加非空约束

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

解决方案

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.