SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Threaded View

  1. #1
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Montreal
    Posts
    406
    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 13:15. Reason: Edited Table


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
  •