CODEWITHSUNDEEP
mysql
Abe Miessler
Abe Miessler

Reputation: 85036

ALTER TABLE not letting me set NULL or default value?

I am trying to change an existing column in a table I have to allow for null values and then set the default value to null. I tried running the following but it does not seem to be updating the table:

mysql> ALTER TABLE answers_form MODIFY sub_id int unsigned NULL DEFAULT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc answers_form;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| answer_id    | int(10) unsigned | NO   | PRI | 0       |       |
| sub_id       | int(10) unsigned | NO   | PRI | 0       |       |
| form_id      | int(10) unsigned | NO   | PRI | NULL    |       |
| value        | varchar(255)     | NO   |     | NULL    |       |
| non_response | bit(1)           | YES  |     | b'0'    |       |
+--------------+------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

Can anyone see what I am doing wrong here?

Upvotes: 6

Views: 2794

Answers (3)

MichaelJCox
MichaelJCox

Reputation: 756

sub_id is listed as a primary key

From the MySQL docs (5.7, but other versions say the same thing):

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently).

As to the discussion about the Non-null columns having a Default of NULL...

The NULL value in the Default column means that there is no default, not that the default is NULL.

Fiddle: http://sqlfiddle.com/#!2/c718d/1

If I create a simple table like so:

CREATE TABLE name_num(
  Number INT PRIMARY KEY, 
  Name TEXT NOT NULL
);

And then do desc name_num, I get:

|  FIELD |    TYPE | NULL | KEY | DEFAULT | EXTRA |
---------------------------------------------------
| Number | int(11) |   NO | PRI |  (null) |       |
|   Name |    text |   NO |     |  (null) |       |

Again, from the MySQL docs:

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as a NOT NULL column (because PRIMARY KEY columns must be NOT NULL), but also assigns it a DEFAULT clause using the implicit default value. To prevent this, include an explicit NOT NULL in the definition of any PRIMARY KEY column.

Upvotes: 1

hd1
hd1

Reputation: 34657

You have 2 non-nullable columns with the default value of null. This shouldn't be allowed by your database engine. If it is, it is rather far from a best practice.

Upvotes: 1

JustDanyul
JustDanyul

Reputation: 14044

its a primary key , mysql doesn't allow any part of the primary key to be null, which does make the fact that it allows a default value of null for the form_id odd, however the docs at

http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

say "Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values".

Just out of curiosity, does it actually allow you to put in null values in the form_id field?

Upvotes: 5

Related Questions