Results 1 to 6 of 6

Thread: FK problem

  1. #1
    Join Date
    Aug 2010
    Posts
    17

    Unanswered: FK problem

    Hi

    This part of code
    PHP Code:
    `FK_user_projectFOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
    Won't work in the following code.

    PHP Code:
    CREATE TABLE IF NOT EXISTS `tbl_issue`
    (
    `
    idINTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `
    namevarchar(256NOT NULL,
    `
    descriptionvarchar(2000),
    `
    project_idINTEGER,
    `
    type_idINTEGER,
    `
    status_idINTEGER,
    `
    owner_idINTEGER,
    `
    requester_idINTEGER,
    `
    create_timeDATETIME,
    `
    create_user_idINTEGER,
    `
    update_timeDATETIME,
    `
    update_user_idINTEGER
    ENGINE InnoDB
    ;
    CREATE TABLE IF NOT EXISTS `tbl_user`
    (
    `
    idINTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `
    emailVarchar(256NOT NULL,
    `
    usernameVarchar(256),
    `
    passwordVarchar(256),
    `
    last_login_timeDatetime,
    `
    create_timeDATETIME,
    `
    create_user_idINTEGER,
    `
    update_timeDATETIME,
    `
    update_user_idINTEGER
    ENGINE InnoDB
    ;
    CREATE TABLE IF NOT EXISTS `tbl_project_user_assignment`
    (
    `
    project_idInt(11NOT NULL,
    `
    user_idInt(11NOT NULL,
    `
    create_timeDATETIME,
    `
    create_user_idINTEGER,
    `
    update_timeDATETIME,
    `
    update_user_idINTEGER,
    PRIMARY KEY (`project_id`,`user_id`)
    ENGINE InnoDB
    ;
    -- 
    The Relationships
    ALTER TABLE 
    `tbl_issueADD CONSTRAINT `FK_issue_projectFOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
    ALTER TABLE `tbl_issueADD CONSTRAINT `FK_issue_ownerFOREIGN KEY (`owner_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
    ALTER TABLE `tbl_issueADD CONSTRAINT `FK_issue_requesterFOREIGN KEY (`requester_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
    ALTER TABLE `tbl_project_user_assignmentADD CONSTRAINT `FK_project_userFOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
    ALTER TABLE `tbl_project_user_assignmentADD CONSTRAINT `FK_user_projectFOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
    -- 
    Insert some seed data so we can just begin using the database
    INSERT INTO 
    `tbl_user`
    (`
    email`, `username`, `password`)
    VALUES
    ('test1@notanaddress.com','Test_User_One'MD5('test1')),
    (
    'test2@notanaddress.com','Test_User_Two'MD5('test2'))

    Where did I go wrong?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, "won't work" is not a valid mysql error message

    got something more substantial?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you also send us the CREATE table statement for tbl_project?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I created a basic table:
    Code:
    CREATE TABLE IF NOT EXISTS tbl_project
    (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(255)
    ) ENGINE = InnoDB;
    And reran your script and it appears to work. You may have forgotten to create this table in your own environment.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Aug 2010
    Posts
    17
    Thanks, it's now working.

    I deleted the whole database instead of emptying it, then redid the process. Strange though that it didn't accept the foreign key earlier.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I is quite possible that there was a table existing in the database with the same name but different fields. The create table would only have happened if the table did not exist and not if the layout was different.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •