Results 1 to 3 of 3

Thread: Mysql Query Help

  1. #1
    Join Date
    Aug 2007
    Location
    Auckland, NZ
    Beans
    196
    Distro
    Ubuntu 9.04 Jaunty Jackalope

    Mysql Query Help

    Hi,

    Just tried to run this query on mysql 5.6. It was previously working on 5.1.

    Anyone have any Idea why it wont let me set this column to null?

    Code:
    mysql> describe diagnostic;
    +--------------------------------+-------------+------+-----+---------+----------------+
    | Field                          | Type        | Null | Key | Default | Extra          |
    +--------------------------------+-------------+------+-----+---------+----------------+
    | diagnostic_id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
    | diagnostic_time                | int(15)     | NO   |     | NULL    |                |
    | diagnostic_user                | int(15)     | NO   |     | NULL    |                |
    | diagnosticdata_time            | int(15)     | NO   |     | NULL    |                |
    | diagnosticdata_user            | int(15)     | NO   |     | NULL    |                |
    | diagnosticdata_name            | varchar(50) | NO   |     | NULL    |                |
    | diagnosticdata_suppliercontact | int(11)     | YES  | MUL | NULL    |                |
    +--------------------------------+-------------+------+-----+---------+----------------+
    7 rows in set (0.00 sec)
    
    mysql> UPDATE diagnostic 
        ->                 LEFT JOIN contact ON diagnosticdata_suppliercontact = contact_id
        ->                 SET diagnosticdata_suppliercontact = NULL
        ->                 WHERE (!contactdata_issupplier) OR (contact.contact_id IS NULL);
    ERROR 1048 (23000): Column 'diagnosticdata_suppliercontact' cannot be null
    mysql>
    Uses: Jaunty J 9.04 & Maverick 10.10

  2. #2
    Join Date
    Jun 2007
    Location
    Porirua, New Zealand
    Beans
    Hidden!
    Distro
    Ubuntu

    Re: Mysql Query Help

    A quick response before I go help Mrs Lisati with the shopping: It's possible that it can't be NULL because it's a KEY field.
    Forum DOs and DON'Ts
    Please use CODE tags
    Including your email address in a post is not recommended


  3. #3
    Join Date
    Nov 2012
    Location
    Halloween Town
    Beans
    Hidden!
    Distro
    Xubuntu Development Release

    Re: Mysql Query Help

    If you want, it is possible to allow NULL values in a foreign key, this property has a relation with another property "ON UPDATE/DELETE SET NULL".

    See this (scroll down until you reach the part about "Deviation from SQL standards").

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
  •