1

Is there a Mysql server setting that will prevent the creation of columns that are NOT NULL but do not have a default value specified?

I'm trying to prevent the creation of fields like this:

ALTER TABLE mytable ADD mydate DATE NOT NULL;

I know I can prevent INSERTs by setting sql_mode=STRICT_ALL_TABLES, but I want to prevent the creation of the columns in the first place.

1

I don't think there is such an option. You could request such at bugs.mysql.com.

I disagree with making NULL (or, for that matter) NOT NULL mandatory. NULL and NOT NULL have important uses in schema design and execution. PRIMARY KEYs must be NOT NULL, and that is implicitly provided. Due to business logic, some columns should not be left out; some should be allowed to be optional.

NULL has a lot of different semantics, and arbitrarily forcing all columns to be NULL begs the issue of which semantics you want. Examples: Optional (product specifications); not yet provided (an end_date); unknown (due to partial data received).

| improve this answer | |
  • I'm not forcing NULL or NOT NULL, I trying to force the default value to be defined. In my DATE example, what value will be read if the default value is not defined? Should it be NULL or something else? I'm trying to avoid mysql error 1364: Field does not have a default value. – lfjeff Jun 25 '19 at 22:19
  • @lfjeff - OK. I like that one. And I would opt for NOT NULL as the default since I have to explicitly think about why I want to use NULL. – Rick James Jun 25 '19 at 22:23
  • Exactly. Try a query like this and you'll see what I'm talking about: SELECT DEFAULT(mydate) FROM mytable LIMIT 1 – lfjeff Jun 25 '19 at 22:29
  • @lfjeff - Hmmm... That is not a function that I have ever found a use for. – Rick James Jun 25 '19 at 22:36
  • I can find the columns that are not fully defined using this query (from codediesel.com/mysql/…): USE information_schema; SELECT TABLE_NAME, COLUMN_NAME FROM columns` WHERE IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NULL AND TABLE_SCHEMA= 'YOUR-DATABASE-NAME'` However, I'm trying to prevent the creation of columns with no default values in the first place. – lfjeff Jun 25 '19 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.