For several hours I have been solving a "banal" problem with creating two simplest tables and connecting them using InnoDB .

This time was enough to hate this technology.

And so the code is:

CREATE database thraph; USE thraph; CREATE TABLE `userdata` ( u_id INT NOT NULL AUTO_INCREMENT, username VARCHAR (16) NOT NULL, password VARCHAR (40) NOT NULL, email VARCHAR (40) NOT NULL, PRIMARY KEY(u_id) ) ENGINE = InnoDB; CREATE TABLE `ect` ( u_id INT UNSIGNED NOT NULL, u_group VARCHAR(6) NOT NULL, u_year VARCHAR(6) NOT NULL, PRIMARY KEY (u_id), FOREIGN KEY (u_id) REFERENCES userdata(u_id) ON UPDATE RESTRICT ON DELETE CASCADE ) ENGINE = InnoDB;

When I try to execute scripts to create table ect , an error constantly crashes.

 #1005 - Can't create table 'thraph.ect' (errno: 150) - Foreign key constraint is incorrectly formed

And even if you manage to create a query and create a table, it is not filled. I only know:

 Если оператор MySQL CREATE TABLE выдает ошибку с номером 1005, и в строке сообщения об ошибке присутствует ссылка на ошибку с номером 150, то произошел сбой создания таблицы из-за того, что ограничения внешнего ключа не были сформированы надлежащим образом.

What does it mean properly? Why is there nothing intelligible on the Internet about this ... What is the way he does not like it)

I would be grateful if you at least partially solve my problem.

  • CREATE TABLE userdata (and FOREIGN KEY (u_id) REFERENCES user_data (u_id) Is this not ochepyatka? - mantigatos
  • A typo when typing, but it has no relation to the health of the request. - zvlex

2 answers 2

When creating foreign keys, the data types in the fields must be the same. Absolutely.

You have a field type in the first table.

  u_id INT NOT NULL AUTO_INCREMENT,

and in the second

  u_id INT UNSIGNED NOT NULL,

Add to the first UNSIGNED and you will be happy.

  • That's right noticed =) - Zowie
  • I want to assure you that I have already done this several times, you know, a table is being created, but it doesn’t want to accept any other DB values ​​at all. - zvlex
  • And yes, you would read the rules for creating a foreign key. The problem would solve itself - Zowie
  • So I will do it. In extreme cases, we will make one table or script in php. - zvlex

Do you not confuse these 2 lines?

 PRIMARY KEY (u_id), FOREIGN KEY (u_id) REFERENCES userdata(u_id)

You have a primary key referencing the prtimary key.

And if you need to solve problems and not study, in your case - you need to use something like mysql workbrench, not hands. A few hours to solve the problem

In general, remember - if you still work with pens and decide to make a foreign key, then do it outside the table initialization code, then it will be easier to catch the error (if it is) and the code will be clearer (if it is important).

  • Of course, they smother, even when PK had other values, produced errors, but this is not the main thing. As I need to solve the problem and at the same time learn and learn, the more the better. Well, thanks for the advice. Maybe you can share a link to some good article about this. - zvlex
  • The best article is a book. While reading "Bowman J.S., Emerson SL. A practical guide to SQL", how to finish - catch me somewhere, I will give more - Zowie
  • Thanks, now let's see what's there. - zvlex