Mysql Help!! Foreign Keys

Discussion in 'OT Technology' started by aredee, Aug 9, 2005.

  1. aredee

    aredee New Member

    Joined:
    Aug 20, 2002
    Messages:
    1,043
    Likes Received:
    0
    Location:
    Washington DC
    here is my sql statement:

    DROP TABLE IF EXISTS `userInfo`;
    CREATE TABLE userInfo (
    `browser` VARCHAR(255) NULL,
    `userIP` TEXT NULL,
    `userOS` varchar(255) default NULL,
    `pages_visited` varchar(255) default NOT NULL,
    `last_login` varchar(255) default NOT NULL, /* works until here */
    FOREIGN KEY (`userID`) FROM userDB(`userID`)
    ) TYPE=InnoDB;


    My Foreign Key is getting error # 1064 which is syntax error. Anyone a SQL guru that could help me out? Thanks in advanced!
     
  2. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,924
    Likes Received:
    11
    Location:
    Los Angeles
    What version of MySQL?

    I don't have too much knowledge of MySQL 5, but I do know that they have enhanced it to support other things such as Stored Procedures and what not, as for Foreign Keys, I still don't know if it has any support. I know MySQL 4 > Foreign Key support is very bad. You'll generally have to handle relationships through an interface and not the Database.
     
  3. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,514
    Likes Received:
    618
    I don't think MySQL supports foreign keys without installing a 3rd party commercial package. I can't remember what it's called though.
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    wait, you're trying to add the foreign key to a column that doesn't exist. The foreign key is just a constraint on a column that should already exist. In other words, you need to add the userId column to your table, and then put the key stuff at the end.
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    InnoDB tables support foreign keys as of MySQL ver. 3.23.44
     
  6. aredee

    aredee New Member

    Joined:
    Aug 20, 2002
    Messages:
    1,043
    Likes Received:
    0
    Location:
    Washington DC
    huh?

    lemme show you what i got:

    DROP TABLE IF EXISTS `userDB`; /* works fine! */
    CREATE TABLE `userDB` (
    `userID` INT NOT NULL AUTO_INCREMENT,
    `username` varchar(8) NOT NULL,
    `userpass` VARCHAR(8) NOT NULL,
    `first_name` VARCHAR(10) NULL,
    `last_name` VARCHAR(15) NULL,
    `address_1` VARCHAR(25) NULL,
    `address_2` VARCHAR(15) NULL,
    `city` VARCHAR(18) NULL,
    `state` VARCHAR(2) NULL,
    `zip_code` TEXT NOT NULL,
    `email` VARCHAR(25) NOT NULL,
    `gender` VARCHAR(1) NULL,
    PRIMARY KEY (`userID`)
    ) TYPE=InnoDB;

    DROP TABLE IF EXISTS `userInfo`;
    CREATE TABLE `userINFO` (
    `browser` VARCHAR( 255 ) NULL ,
    `userIP` TEXT NULL ,
    `userOS` varchar( 255 ) NOT NULL ,
    `pages_visited` varchar( 255 ) NOT NULL ,
    `last_login` DATE NOT NULL ,
    INDEX (userID),
    CONSTRAINT FOREIGN KEY ( `userID` ) REFERENCES `userDB` ( `userID` ) ON DELETE CASCADE ON UPDATE CASCADE
    ) TYPE = InnoDB;


    Any ideas now?
     
  7. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    you still need to DEFINE the column in your child table:

    DROP TABLE IF EXISTS `userInfo`;
    CREATE TABLE `userINFO` (
    `userID` INT NOT NULL,
    `browser` VARCHAR( 255 ) NULL ,
    `userIP` TEXT NULL ,
    `userOS` varchar( 255 ) NOT NULL ,
    `pages_visited` varchar( 255 ) NOT NULL ,
    `last_login` DATE NOT NULL ,
    INDEX (userID),
    CONSTRAINT FOREIGN KEY ( `userID` ) REFERENCES `userDB` ( `userID` ) ON DELETE CASCADE ON UPDATE CASCADE
    ) TYPE = InnoDB;


    I don't have mysql here at work, so i'm not sure if you have the syntax exactly right, but this is the general idea. You need a column in the SECOND table labeled userid
     
  8. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,514
    Likes Received:
    618
    That's the one. :)
     
  9. aredee

    aredee New Member

    Joined:
    Aug 20, 2002
    Messages:
    1,043
    Likes Received:
    0
    Location:
    Washington DC
    Last edited: Aug 10, 2005
  10. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what type of error is it throwing?
     

Share This Page