1

I'm using MySQL 5.5.37 on OS X 10.9.1. I have a NOT NULL column of type DATETIME. Without using triggers, how can I block a value of '0000-00-00 00:00:00' from getting into my column? I'll accept a value of CURRENT_TIMESTAMP in place of all the zeroes.

I tried:

SET sql_mode = 'NO_ZERO_DATE';

I was still able to insert a zero date into my table after running the above.

tadman
  • 208,517
  • 23
  • 234
  • 262
Dave
  • 15,639
  • 133
  • 442
  • 830

3 Answers3

5

Without using triggers (your stated condition), you can't. However, according to the MySQL documentation,

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

  • If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

  • If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

  • If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

So you should ensure that strict SQL mode is also enabled. But even then, as a commenter has pointed out, this won't necessarily work. And check constraints are ignored by MySQL also, so that won't work either. If you don't want to use a trigger, you're stuck.

Community
  • 1
  • 1
citsonga
  • 323
  • 1
  • 2
  • 9
  • This only explains why `NO_ZERO_DATE` doesn't work. It doesn't answer the question of what to do instead. You edited your answer to bold the part about strict SQL mode, but the text you quote shows the error can *still* become a warning (causing the bogus date to get inserted) even in strict mode. –  Apr 29 '14 at 21:30
  • I thought it was apparent, I put in bold what OP needs to do and added a sentence at the end saying it again. – citsonga Apr 29 '14 at 21:31
  • [Here's an SQL Fiddle](http://www.sqlfiddle.com/#!2/3c32d/3) *showing* that it doesn't actually prevent '0000-00-00' from being inserted. –  Apr 29 '14 at 21:39
  • @hvd interesting, if OP insists on using DATETIME and not TIMESTAMP, and won't use triggers, not sure anything will help. – citsonga Apr 29 '14 at 21:50
  • 1
    Yes, I think you're right that a trigger is the only way to reliably prevent it. –  Apr 29 '14 at 21:52
  • @Dave not if it is trying to insert 0's, you'd still need a trigger to replace this. But [you can get TIMESTAMP to replace a NULL insert with a default of `CURRENT_TIMESTAMP`.](http://stackoverflow.com/a/168832/3583048) – citsonga Apr 29 '14 at 22:38
0

The right SQL answer would be to add a check constraint on the column:

create table foo
(
  ...
  bar datetime not null check ( bar != '0000-00-00 00:00:00' ) ,
  ...
)

However, mySql, while perfectly happy to parse and nominally 'accept' check constraint definitions, it silently ignores them, per a sotto vocce comment in the create table documentation:

The CHECK clause is parsed but ignored by all storage engines.

To that end, you probably want to add a before insert trigger to enforce the constraint. You should probably also enforce that the date is actually a valid date, since apparently mySql doesn't even enforce that minimal constraint in date and datetime columns:

MySQL enables you to store certain incorrect date values into DATE and DATETIME columns (such as '2000-02-31' or '2000-02-00'). The idea is that it is not the job of the SQL server to validate dates. If MySQL can store a date value and retrieve exactly the same value, MySQL stores it as given. If the date is totally wrong (outside the server's ability to store it), the special “zero” date value '0000-00-00' is stored in the column instead.

See this question for details on how to create such a trigger: Mysql CHECK Constraint

Community
  • 1
  • 1
Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
-1

Your Database should not be responsible for validating the data your script is trying to insert.

It should check, whether the given data matches the boundaries of the column (not null, type and length) but not really care about the actual content.

If you set columns to NOT NULL, MySQL will still accept the empty string, 0 or 0000-00-00 as "valid" values for string / int / date.

The Database should store Data - not validate it. If you want to reject invalid data, this is a task the programming language of your choice should perform. Otherwhise (overstated) you could connect the UI with the database and omit any "backend" logic.

dognose
  • 20,360
  • 9
  • 61
  • 107
  • @GordonLinoff Feel free to even downvote - Why should a database take over "logical" decisions? What if somebody does not want to allow the date "XXXX-12-24" cause that's christmas? Should the database provide an option for that? – dognose Apr 29 '14 at 21:40
  • . . I stopped downvoting over a year ago, but this does stretch my conviction. Databases are actually a much better place to validate data than applications. However, that is not germane to the question being asked. – Gordon Linoff Apr 29 '14 at 21:43
  • @GordonLinoff Well, I disagree on that. If the Data matches the boundaries of the column, the database should store it. Decisions about the actual *content* should be up to the code using the database - like "not allow dates in the past", "not allow birthdays where the person would be older than 150 years" and so on. But Maybe you have an example where a database could be "a much better place to validate data"? – dognose Apr 29 '14 at 21:47
  • 1
    . . Every reasonable application I've worked on. The database is the data store. That is why databases support `check` constraints and other methods for validating data. A database should only store data that meets the requirements of the application. The closer the checks are to the data, the more likely that they will actually be consistently implemented. – Gordon Linoff Apr 29 '14 at 22:01
  • @GordonLinoff I absolutely agree, that the Database should only contain valid data. But valid in the terms of FORMAT and TYPE - not in terms of content. If one wants to store "false" in a boolean column, that should work! There should be NO Contraint allowing just "true"... Well okay: Different Opinions. I will keep your approach in mind and maybe find a usecase for it - even if it is "now" hard to imagine. – dognose Apr 29 '14 at 22:08
  • This is pure opinion and does not answer the question asked. – cimmanon Oct 09 '14 at 14:03