SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Montreal
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Create Foreign Key in MYSQL

    Hi

    I was wondering on how to edit a table in order to have a foriegn key.

    I have 2 tables, administrators and administrators types.
    One column in administrators would be called user_type which should reference the administrators_types table.

    Any ideas??

    Thanks,
    Chris

    Here is the DB:
    CREATE TABLE IF NOT EXISTS `administrators` (
    `id` smallint(6) NOT NULL default '0',
    `username` varchar(30) NOT NULL default '',
    `password` varchar(30) NOT NULL default '',
    `email` varchar(255) NOT NULL default '',
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(30) NOT NULL,
    `user_type` smallint(6) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE IF NOT EXISTS `administrator_types` (
    `id` smallint(6) NOT NULL,
    `name` varchar(20) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Here is the add foreign key command:
    ALTER TABLE administrators ADD FOREIGN KEY ( user_type ) REFERENCES administrator_types( id )
    Here is the error:
    #1005 - Can't create table './workspace33/#sql-931_1488c.frm' (errno: 150)
    Last edited by digadesign; Dec 5, 2008 at 12:15. Reason: Edited Table

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try adding an index first --
    Code:
    ALTER TABLE administrators 
    ADD INDEX user_type_ix ( user_type );
    
    ALTER TABLE administrators 
    ADD CONSTRAINT user_type_fk
    FOREIGN KEY ( user_type ) REFERENCES administrator_types( id ) ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Montreal
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply

    I did the first step and worked:
    ALTER TABLE administrators
    ADD INDEX user_type_ix ( user_type );
    But the second part failed with the same error:
    ALTER TABLE administrators
    ADD CONSTRAINT user_type_fk
    FOREIGN KEY ( user_type ) REFERENCES administrator_types( id ) ;
    Error:
    #1005 - Can't create table './workspace33/#sql-931_15c04.frm' (errno: 150)
    Could this be because of one table is:
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    and the other is:
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    Quote Originally Posted by r937 View Post
    try adding an index first --
    Code:
    ALTER TABLE administrators 
    ADD INDEX user_type_ix ( user_type );
    Code:
    ALTER TABLE administrators 
    ADD CONSTRAINT user_type_fk
    FOREIGN KEY ( user_type ) REFERENCES administrator_types( id ) ;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by digadesign View Post
    Could this be because of one table is:
    yes it most certainly could

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Montreal
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey

    Do you know any good links or ebooks where I can learn wbout foreign keys in mysql?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    just do a search for foreign key

    the principle is the same in all databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •