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
    Never assume that information you find using a search engine is up-to-date.
    Please use CODE tags.
    A low-volume blog

  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").
    Please mark your thread as solved if you get a satisfactory response
    PopularPages - A Ubuntu Documentation Search Tool
    Ubuntu membership via Forums contributions

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
  •