SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Not Null

  1. #1
    SitePoint Addict AfroNinja's Avatar
    Join Date
    Oct 2006
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not Null

    I can't seem to find a comprehensive or accurate answer on this subject.

    Is it more work and/or overhead for mysql to mark a column as not null? IE, should I keep columns as NULL whenever possible?

    If the column is allowed to be null but I also provide a default value, will the default be chosen if no value is provided?

    If the column is not allowed to be null and I don't provide a default value, will a default be chosen? IE 0, false, "", etc
    The Flash Gaming Network
    Editorial reviews for the latest flash games!
    Afro Ninja Productions
    Original flash games and content from a guy with an afro

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it more work and/or overhead for mysql to mark a column as not null?
    Yes, it's a constraint, which means that the engine will have to check conditions upon insert/update.
    But that overhead is so infinitesimal, that it really don't matters.

    should I keep columns as NULL whenever possible?
    No, the overhead is so small, that implementing the checks in your logic will even cost more than letting the db do the check.
    Now, of course, it's better tho have a check in your logic, and the db constraint should only be there to avoid something that you have missed to go through.

    If the column is allowed to be null but I also provide a default value, will the default be chosen if no value is provided?
    Yes

    If the column is not allowed to be null and I don't provide a default value, will a default be chosen? IE 0, false, "", etc
    It depends of your settings and versions.
    Mysql 4 was putting a blank character where you had a non null column but you didn't gave a value (which is plain wrong).
    Mysql 5 raises an exception, and refuse the insert, because of the non-null constraint.
    Now, mysq5 has several level of backward compatibility with mysql 4, so I don't know if it emulate again this behavior.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •