MySQL: COLUMN_DEFAULT is always NULL for non null default values

Question

CREATE TABLE `test` (
`id` INT(10) UNSIGNED NOT NULL,
`c1` VARCHAR(60) NULL DEFAULT NULL,
`c2` VARCHAR(60) NOT NULL,
PRIMARY KEY (`id`)
 )

In the information_schema.columns.COLUMN_DEFAULT contains NULL for both c1,c2 columns but c2 doesn't have default value.

How to get the real COLUMN_DEFAULT value?

COLUMN_DEFAULT contains NULL for both c1,c2 columns is not correct, you c2 column is set NOT NULL, then how it is come null ? and what did you mean by COLUMN_DEFAULT value ? — Nikson Kanti Paul
– Nikson Kanti Paul, Commented Jan 22, 2013 at 6:36
Check schemata and you will see that information_schema.columns.COLUMN_DEFAULT is set to NULL (if you do not mention default value). Select queries to schemata return NULL value for COLUMN_DEFAULT. — Mansoor
– Mansoor, Commented Jan 22, 2013 at 6:42

Bernhard Barker · Accepted Answer · 2013-01-22 07:31:54Z

2

In information_schema.columns, if COLUMN_DEFAULT is NULL and IS_NULLABLE is NO then it doesn't have a default.

But basically you can see it as: the default, if not specified, is always NULL. So, c2 isn't allowed to be NULL, but it will default to NULL, thus will always need to be specified on insert statements.

Note: a field specified as NOT NULL doesn't need to be able to represent NULL, thus it can theoretically take up (slightly) less space.

edited Jan 22, 2013 at 7:31

answered Jan 22, 2013 at 6:59

Bernhard Barker

55.7k14 gold badges111 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mansoor Over a year ago

So how to get the real default value from schemata?

Mansoor Over a year ago

If you try to INSERT INTO test (id,c1) VALUES (1,NULL) then the error will be occurred that c2 doesn't have default value. So how can I know if c2 default value is set to null or c2 doesn't have default value?

Mansoor Over a year ago

So how can I know if c2 default value is set to null and I can ignore c2 column in INSERT statement or c2 doesn't have default value and I must include c2 column value?

Bernhard Barker Over a year ago

If COLUMN_DEFAULT is NULL and IS_NULLABLE is NO then you need to specify it.

Mansoor Over a year ago

And if IS_NULLABLE is YES for a column but default value is not specified then I can ignore this column in INSERT statement?

|

Collectives™ on Stack Overflow

MySQL: COLUMN_DEFAULT is always NULL for non null default values

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related