Create Foreign Key in MYSQL

[digadesign]

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)

[r937]

try adding an index first --

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 ) ;

[digadesign]

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;

try adding an index first --

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 ) ;

[r937]

Could this be because of one table is:

yes it most certainly could

[digadesign]

Hey

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

[r937]

just do a search for foreign key

the principle is the same in all databases