CODEWITHSUNDEEP
mysqldefault-value
Sangram Anand
Sangram Anand

Reputation: 10844

How to set mysql Default value NONE

I am using a table "media_content" which has columns

id varchar(50) NOT NULL,
shorttitle varchar(200) DEFAULT NULL,
sourceimage blob

Later i modified the sourceimage column to default values as NULL

ALTER TABLE media_content
MODIFY COLUMN sourceimage VARCHAR(250) DEFAULT NULL;

Now i want to make the sourceimage column default as NONE, is it possible to do that?

Upvotes: 2

Views: 25825

Answers (4)

user2616131
user2616131

Reputation: 1

I could be wrong, but I think the original post is asking how to remove a default value, not add the string literal "none". If so, you can do this:

ALTER TABLE media_content ALTER COLUMN sourceimage DROP DEFAULT;

Upvotes: 0

Matthew Slyman
Matthew Slyman

Reputation: 356

For me, the issue (and the relationship between NULL || NOT NULL, and default values) becomes clearer when you consider that:

NOT NULL ⇒ REQUIRED: there must either be a default value, or else record insertion without a specified value must not be allowed (specifying a default value, or selecting "None"⇒no default specified; might indicate your preference between these two options: the latter option meaning that insertion without a value in this field should be rejected with an error); whereas:

NULL ⇒ NOT REQUIRED — NULL signifies that no value was specified for a field, or, the value is unknown. (If a field is "NULLable", then it would be nonsense to try to say you don't want to specify the default value; since according to nullability, insertion is going to be allowed anyway even if you don't specify this field value! The field must therefore have a default: whether that is NULL or some specific value.)

If you are using phpMyAdmin, you should visit the Structure tab of the relevant table, find the relevant column definition, and select "Change". In the "Default" drop-down, you will see the option, "None": select that, and press "SAVE". Taking note of the SQL command this generates, you will see something like this:

ALTER TABLE  `…_tbl` CHANGE  `…_fld` `…_fld` TEXT NOT NULL;

HOWEVER, if we try to specify, in the user-interface, that the default should be "None" but that NULL should be allowed; then the default will instead revert to "" (empty string) or "0000-00-00 00:00:00" (in the case of a DATETIME field), etc. So the key here is whether the column (field) is NULLABLE or not!

This "None" option may be partly an artefact of the abstraction used by phpMyAdmin to represent what's actually going on in the underlying database (which is something different).

Before changing your field to a NOT NULL type; first check the existing values, and make sure that they will be automatically changed in a way you approve.

In other words: NULL / NOT NULL, and default values; are essentially related to each other: user-interfaces that don't usefully represent this relationship by constraining the combinations of values you can specify, are ergonomically deficient. phpMyAdmin and perhaps other tools, could be improved in this respect.

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80629

Using the same ALTER syntax, the default value can be changed to NONE as follows:

ALTER TABLE media_content
MODIFY COLUMN sourceimage VARCHAR(250) DEFAULT 'NONE';

Upvotes: 3

eggyal
eggyal

Reputation: 125835

You can specify the default value (of a string-type column) to be a string literal constant, such as 'NONE', but be aware that such a value has a very different meaning indeed to NULL (which indicates no value at all).

As documented under Working with NULL Values:

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

[ deletia ]

A common error when working with NULL is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL means “not having a value.”

If you merely wish to present NULL values to your users in some other way, you should handle that at the presentation layer of your application.

Upvotes: 4

Related Questions