0

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.

2
Contributors
12
Replies
13
Views
5 Years
Discussion Span
Last Post by rotten69
0

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.

0

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

0

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.

Edited by pritaeas: n/a

0

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?

0

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.

0

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.

0

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?

Edited by rotten69: n/a

0

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

Edited by rotten69: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.