SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    foreign keys not updating

    Hey guys,

    I'm not sure what I'm doing wrong. I have about 15 tables, but I'll give one example.

    Here's the queries:

    Code:
    CREATE TABLE `phpvms_airlines` (
        `id` INT NOT NULL AUTO_INCREMENT ,
        `code` VARCHAR( 3 ) NOT NULL ,
        `name` VARCHAR( 30 ) NOT NULL ,
        PRIMARY KEY ( `id` ),
        UNIQUE KEY `code` (`code`)
    )ENGINE=INNODB;
    
    CREATE TABLE `phpvms_ranks` (
        `rankid` int(11) NOT NULL auto_increment,
        `rank` varchar(32) NOT NULL default '',
        `minhours` smallint(6) NOT NULL default '0',
        PRIMARY KEY  (`rankid`),
        UNIQUE KEY `rank` (`rank`)
    )ENGINE=INNODB;
    
    INSERT INTO `phpvms_ranks` VALUES(1, 'New Hire', 0);
    
    CREATE TABLE `phpvms_pilots` (
        `pilotid` int(11) NOT NULL auto_increment,
        `firstname` varchar(25) NOT NULL default '',
        `lastname` varchar(25) NOT NULL default '',
        `email` varchar(32) NOT NULL default '',
        `code` varchar(3) NOT NULL default '',
        `location` varchar(32) NOT NULL default '',
        `password` varchar(32) NOT NULL default '',
        `salt` varchar(32) NOT NULL default '',
        `lastlogin` date NOT NULL default '0000-00-00',
        `totalflights` int(11) NOT NULL default '0',
        `totalhours` float NOT NULL default '0',
        `rank` varchar(32) NOT NULL default 'New Hire',
        `confirmed` enum('y','n') NOT NULL default 'n',
        `retired` enum('y','n') NOT NULL default 'y',
        PRIMARY KEY  (`pilotid`),
        UNIQUE KEY `email` (`email`),
        FOREIGN KEY (`code`) REFERENCES phpvms_airlines(`code`) ON UPDATE CASCADE,
        FOREIGN KEY (`rank`) REFERENCES phpvms_ranks(`rank`) ON UPDATE CASCADE
    )ENGINE=INNODB;
    So when I change "New Hire" to something else, in the ranks table, it doesn't update in the pilots table.

    This same thing whenever I update the other fields.
    The engine is correct, it seems. And it seems to be creating the keys (when I mess with them, I do get errors, like if I change the column name or something).

    Same thing happens when I update the 'code' in the airlines table.

    So, what am I missing, or am I getting the usage/concept completely wrong?

  2. #2
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Example:

    Code:
    mysql> select * from phpvms_airlines;
    +----+------+--------+
    | id | code | name   |
    +----+------+--------+
    |  1 | VMS  | phpvms |
    +----+------+--------+
    1 row in set (0.00 sec)
    
    mysql> select pilotid, code from phpvms_pilots;
    +---------+------+
    | pilotid | code |
    +---------+------+
    |       1 | VMS  |
    +---------+------+
    1 row in set (0.00 sec)
    
    mysql> update phpvms_airlines set code='VMX' where code='VMS'; select pilotid,code from phpvms_pilots;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    +---------+------+
    | pilotid | code |
    +---------+------+
    |       1 | VMS  |
    +---------+------+
    1 row in set (0.00 sec)
    
    mysql> select * from phpvms_airlines;
    +----+------+--------+
    | id | code | name   |
    +----+------+--------+
    |  1 | VMX  | phpvms |
    +----+------+--------+
    1 row in set (0.00 sec)
    As you can see, there's no change

  3. #3
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And another aside to my question - is it safe to have InnoDB, from a distribution standpoint, or should I just forget the foreign keys and stick to myisam. Or worded better, is InnoDB ok for compatibililty, will everyone have it?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i ran your code, and it updated fine for me, on mysql version 4.1.20

    what version are you on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Odd.

    Code:
    -bash-3.00$ mysql --version
    mysql  Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3
    site5 is my host.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please provide the output of:
    Code:
    show variable like 'have%';

  7. #7
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gives an error:
    Code:
    mysql> show variable like 'have%';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
    that corresponds to your MySQL server version for the right syntax 
    to use near 'variable like 'have%'' at line 1

  8. #8
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nvm, I just copy pasted the section:

    Code:
    | have_archive                    | NO                                       |
    | have_bdb                        | NO                                       |
    | have_blackhole_engine           | NO                                       |
    | have_compress                   | YES                                      |
    | have_crypt                      | YES                                      |
    | have_csv                        | NO                                       |
    | have_example_engine             | NO                                       |
    | have_geometry                   | YES                                      |
    | have_innodb                     | YES                                      |
    | have_isam                       | NO                                       |
    | have_merge_engine               | YES                                      |
    | have_ndbcluster                 | NO                                       |
    | have_openssl                    | NO                                       |
    | have_query_cache                | YES                                      |
    | have_raid                       | NO                                       |
    | have_rtree_keys                 | YES                                      |
    | have_symlink                    | YES

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sorry, that should have been "show variables".

    anyway, the only other thing i can think of is that someone has FOREIGN_KEY_CHECKS set to 0. take a look at that variable.

  10. #10
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    sorry, that should have been "show variables".

    anyway, the only other thing i can think of is that someone has FOREIGN_KEY_CHECKS set to 0. take a look at that variable.
    Something weird,

    Code:
    mysql> SET FOREIGN_KEY_CHECKS=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET FOREIGN_KEY_CHECKS=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET FOREIGN_KEY_CHECKS=1;
    Query OK, 0 rows affected (0.00 sec)
    And it's now showing up in SHOW VARIABLES;
    This is a problem, I have to enforce this through code now, since it might not be reliable on customer's systems.

  11. #11
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It somehow magically started working. I contacted my host, I'm not sure what they did, but they emailed me back saying there wasn't any issues, but it started working.

    So I'm not sure.

    But thanks everyone! I guess I'll also run the foreign_keys command first thing after connect.


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
  •