SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,762
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    MediumInt field cannot be set to Nullable

    In MySQL, I have a visitor_log table that looks like this...

    Code:
    FIELD				NULL	DEFAULT
    ------				-----	--------
    id (UK)				No
    member_viewed_id (P1)(FK)	No
    visitor_id (P2)(FK)		No	0
    ip				No
    hostname			No
    created_on (P3)			No
    updated_on			Yes

    Based on some previous advice, I was told that is it okay to store a NULL in the "visitor_id" field when someone is a non-registered Visitor.

    In phpMyAdmin, I changed the "visitor_id" field to Null = "Yes" and left Default blank, but when I save the table phpMyAdmin is not accepting these changes?!

    What is wrong?

    And how do I make it so I can INSERT a NULL into the "visitor_id" field when logging a Non-Member Visitor?

    Sincerely,


    Debbie

    P.S. Because I used to INSERT a "0" for Non-Member Visitors, my table has tons of records with visitor_id = 0. But that shouldn't matter if I am just trying to make the field Nullable?!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you please do a SHOW CREATE TABLE and we'll take it from there

    it's always best to get the actual mysql version rather than phpmyadmin's interpretation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,762
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you please do a SHOW CREATE TABLE and we'll take it from there

    it's always best to get the actual mysql version rather than phpmyadmin's interpretation
    Based on my research, the problem seems to be that "visitor_id" is part of the Composite PK. (If it weren't for that, things should work.)

    With no other real options, I decided to create a "dummy" Member with an ID=0, and I changed my code so that I INSERT a "0" when a Non-Member is viewing a Member's Profile.

    So instead of looking for NULLS, I just look for zeros.

    Seems to work.

    Sincerely,


    Debbie


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
  •