SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: Create Foreign Key in MYSQL
-
Dec 5, 2008, 13:10 #1
- 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;
ALTER TABLE administrators ADD FOREIGN KEY ( user_type ) REFERENCES administrator_types( id )
#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
-
Dec 5, 2008, 13:24 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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 ) ;
-
Dec 5, 2008, 14:30 #3
- 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 );
ALTER TABLE administrators
ADD CONSTRAINT user_type_fk
FOREIGN KEY ( user_type ) REFERENCES administrator_types( id ) ;
#1005 - Can't create table './workspace33/#sql-931_15c04.frm' (errno: 150)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and the other is:
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Code:ALTER TABLE administrators ADD CONSTRAINT user_type_fk FOREIGN KEY ( user_type ) REFERENCES administrator_types( id ) ;
-
Dec 5, 2008, 15:03 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 12, 2008, 08:48 #5
- 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?
-
Dec 12, 2008, 09:16 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
just do a search for foreign key
the principle is the same in all databases
Bookmarks