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

score 326 · Accepted Answer · edited Apr 20 '14 at 15:25

326

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.

edited Apr 20 '14 at 15:25

Mark Amery

143,130
81
406
459

answered Jun 10 '11 at 10:45

Shakti Singh

84,385
21
134
153

@ShaktiSingh What's the difference between `modify` column and `change` column? – Pacerier Jul 06 '12 at 08:57
4

@Positive why did you write `INT(11)` and not just `INT`? What is the effect of the `11` ? – Suzanne Soy May 13 '13 at 11:40
2

The 11 is just an example, it sets the length of the P_Id. i am not sure if it is necessary to add it again even though you might not even want to change it. – Gerard Nov 10 '13 at 20:38
2

@Pacerier with `change` you modify the column's name – Javier P Mar 14 '14 at 18:00
@JavierP, I meant what is the difference between modify column and change with the same name? – Pacerier Mar 17 '14 at 18:57
@Pacerier There is no difference. – Mark Amery Apr 20 '14 at 15:14
I had to specify the column name twice. This means: ALTER TABLE Person Person P_Id INT(11) NOT NULL Default 12345; The default is just an example. The column name twice because first you tell which column to modify and then how it should be named after the edit. – Valentin Grégoire Oct 13 '15 at 07:50
1

@Victor `MODIFY` is also supported by Oracle. And PostgreSQL doesn't support `CHANGE` instead it provides an `ALTER [COLUMN]` statement. – Mr. Deathless Nov 12 '15 at 09:13
@Gerard the (11) doesn't set the length (whatever that would mean), it changes the number of 0 to pad in front of an int when rendered with the 0 padded option. In other words it's irrelevant in 99.9% of cases, and also it's the default. – Fabien Warniez Apr 25 '17 at 16:50
1

note to self: structured query language isn't a standard query language... – Dmytro Mar 15 '18 at 19:35

score 25 · Answer 2 · edited Apr 02 '20 at 07:23

25

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.

edited Apr 02 '20 at 07:23

AymDev

6,626
4
29
52

answered Oct 12 '15 at 07:32

Maran Manisekar

821
1
8
9

5

You perform MODIFY operation to change but reverse is not possible ? – Navrattan Yadav Mar 31 '16 at 10:28
1

Oddly I was getting an error (MySQL 5.6, Workbench 6.3) changing/modifying a column I'd named null_heart_rate_count, error was #1138, Invalid use of NULL value. I had to drop and add the column instead. – William T. Mallard Aug 11 '17 at 17:03
@NavrattanYadav I think he meant to say rename, not reverse – shaahiin Oct 28 '19 at 08:40
2

@WilliamT.Mallard I was getting the same error. In my case it was because the column was already containing a NULL value in one row. After changing values to somenthing other than NULL, the query performed successfully. – Paolo42 Jun 17 '22 at 12:47

score 15 · Answer 3 · answered Feb 25 '21 at 04:58

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)

How to add not null constraint to existing column in MySQL

3 Answers3

Linked

Related