MySQL errors

[rotten69]

I'm trying to make a table and mysql is giving me this error:

#1005 - Can't create table 'likes.users' (errno: 150)

That would be great to see a website that explains mysql errors like this for oracle http://ora-code.com/

CREATE TABLE IF NOT EXISTS `users` (
 `user_id` int(2) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) NOT NULL,
 `article_id` int(11) NOT NULL,
 PRIMARY KEY (`user_id`),
 CONSTRAINT `fk_article_id` FOREIGN KEY (`article_id`) REFERENCES `articles` (`article_id`)  ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

I can't see anything wrong with the create statement.

[pritaeas]

http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html

Foreign key problem, as described in the first bullet point.

[rotten69]

I personally don't see anything wrong with the syntax or the foreign keys that I am trying to make. Can you pick any error in the create statement?

Thanks, pal.

[rotten69]

Yes, I do have another table called articles. Therefore, mySQL server shouldn't refuse my request.

[pritaeas]

Perhaps a mismatch in columns types (guessing here) ? Is there already data in articles ?

[rotten69]

It doesn't contain any data.

[pritaeas]

I just tried to run the following on my test database, and it executes just fine.

CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`article_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` INT(2) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(20) NOT NULL,
  `article_id` INT(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_article_id` 
    FOREIGN KEY (`article_id`) 
    REFERENCES `articles` (`article_id`)  
    ON UPDATE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

The ON DELETE SET NULL is not possible. Your article_id is set to NOT NULL. This is causing the issues.

[rotten69]

From line 11 to 14, Would that be problematic to have all these SQL statements on just one line? (that is what I initially had)

What does this line mean? AUTO_INCREMENT=12?

[pritaeas]

No, spacing doesn't matter, I just added that for readability.

That means the next auto id generated will be 12, remove that if you want to start at 1 again.

[rotten69]

Thanks for explainin that to me. Now, I created the foreign keys that I needed for the tables. However, I was unable to add/specify restriction on the keys. ON UPDATE CASCADE ON DELETE SET NULL .. I don't know why mysql isn't happy to accept my commands.

[rotten69]

I don't want to recreate the FKs. I just want to modify them to add these properties in ON DELETE SET NULL and so on. Is there a modify-constraint statement?

[pritaeas]

If you change `article_id` INT(11) NULL in users then it should work.

[rotten69]

I created the foreign key on article_id. But, I couldn't specify this property at the time of creation. ON UPDATE CASCADE.