Hey,
I can’t seem to add a foreign key and I’m not sure why. Here is the error message:
1005 - Can’t create table ‘dummy.#sql-cc4_b8’ (errno: 150)
It happens when I try this:
ALTER TABLE t_comments
ADD FOREIGN KEY (userid)
REFERENCES users(id)
The table users is MYISAM and the t_comments is innoDB, I’m not sure if thats why its causing an issue?
thank you
both tables have to be innodb, i believe
Yes that’s the issue. Both should be InnoDB.
If both are MyISAM you wouldn’t get an error, but MyISAM doesn’t enforce foreign keys so it wouldn’t be useful either.
I have a contraint UNQIUE on a column called alias in my users table. Would I take a hit in performance when doing an INSERT? Seeing as it has to search al he rows for it. I hear innoDB isn’t good for that
no, no “hit”
and innodb is just fine at doing an index lookup – you need to start listening to other sources 
I dont understand what you mean by “other resources” ?
he said “I hear innoDB isn’t good for that”
so i said “you need to start listening to other sources”
get it?
aaahh I get you now.
Hehehe thank you.
I’m having a small issue
When I do an INSERT it fails and gives me an error message
Cannot add or update a child row: a foreign key constraint fails
When I do the INSERT
INSERT INTO help (userid, title, message, date)
VALUES (1, "The title", "The content!", NOW())
userid column has a FOREIGN KEY that references the users table id column. Both are innoDB this time.
apparently you do not have a row in the users table with that id value
It’s a dummy database hosted locally. It only has one row with the value of 1… 
whose thread is this? i feel like i’m trying to answer similar questions from two different guys
Sorry same person. I was answering from my iPhone and my PC.
well, i’m sorry to have to tell you this, but it is against sitepoint rules to have more than one account
please decide which one you would like to keep, because a moderator will soon be contacting you to have one of your accounts banned
meanwhile, could you dump the tables please – this involves using mysqldump which generates the CREATE TABLE statements as well as the INSERT statements to populate the rows
thanks
No biggy.
Here is the information you asked for.
Users table:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`alias` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`date` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `alias` (`alias`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Theres only one row in the users table, which is me.
INSERT INTO users (alias, email, password, date)
VALUES ("ybh", "email@email.com", "password", NOW())
help table:
CREATE TABLE IF NOT EXISTS `help` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`help` varchar(4000) NOT NULL,
`date` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
When I try to do an INSERT in help it gives me the error message.
INSERT INTO HELP (userid, title, message, DATE)
VALUES (1, "The title", "The content!", NOW())
thanks
EDIT:
Not sure if you need this:
ALTER TABLE `help`
ADD FOREIGN KEY ( `userid` )
REFERENCES `dummy`.`users` (`id`);
I dropped the tables and recreated them and it seems to have fixed the issue. I think the problem came from altering the table while already containing an index as MyISAM with the same name and then switching over to innoDB and creating a foreign key with the same name.
whether it was the cause I can’t say but, I have found that foreign keys have to have a unqiue (index) name so following on from there, I make sure that PKs are caled ‘id’ and their respective FK is given the full name eg user_id.
For foreign keys, if you give one a nmae in one tanle, that name has to be unique throughout the db.
(it’s the index name I mean, not the col name).
hth
bazz