Can' create table XXX errno:150

[marsheng]

I get this error when trying to create a New relationship.

Can't create table 'xxx.#sql-16c-b4b' (errno:150)

Error Relation "clubs_register.racepoints.fk_racepoints_races_raceid" missed. Series.dbd

Any suggestions?

[alexa]

Please try to add the FK using SQL script and then execute the following query straight away in order to obtain the error description:

SHOW ENGINE INNODB STATUS

[marsheng]

I'm new to this. I have been using master detail in Delphi for my apps so I have never had to do any SQL before.

I'm using this tool to document my program. I have managed to create some relationships with the Database Design tool using my existing db, but a few relationships give the errors.

I'm not sure where to enter the SQl in the Database Design tool page.

Is there a way to view the script of an existing relationship?

[alexa]

You would need to create a *.sql file (select 'Tools -> Devart Tools -> New File' from the main menu), type the SQL script in the SQL document, and then execute it (F5).

[marsheng]

alter table bikes add constraint foreign key fk_b (MemID) references members(memID)

Many bikes to 1 member.

This is what I get

1 Cannot add or update a child row: a foreign key constraint fails (`clubs_register`.<result 2 when explaining filename '#sql-16c_ba5'>, CONSTRAINT `#sql-16c_ba5_ibfk_1` FOREIGN KEY (`MemID`) REFERENCES `members` (`MemID`)) SQL1.sql 1 1

[alexa]

Could you please provide us the CREATE TABLE statements of these two tables (select 'View -> Devart windows -> Database Explorer' from the main menu, right-click the table in Database Explorer and select 'Generate Script As -> CREATE' from the popup menu) and the data from the 'MemID' column for both the tables?

[marsheng]

No Create ??

[alexa]

Unfortunately, this feature is not available in the Express version of dbForge Studio for MySQL. You would need to install the Trial version where all features are implemented in full: http://www.devart.com/dbforge/mysql/stu ... nload.html

[tranpl]

Hello. I am having this same issue and I have the dbForge for Mysql full version. Any suggestions?

[alexa]

Could you please select 'Help -> About' from the main menu and provide us the screenshot displaying the 'About dbForge Studio for MySQL' window and the popup menu of the table in Database Explorer just like the other user provided in the previous post?

[blyxx86]

I am also having this issue, with only certain tables.

I used to use MySQL Workbench, but have switched because there are too many bugs and inconsistencies when trying to sync db changes.

Anyways, I have the following tables:

user

Code: Select all

USE my_db;
CREATE TABLE my_db.user (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  email varchar(200) NOT NULL,
  name varchar(100) NOT NULL,
  password_hash varchar(100) NOT NULL,
  password_salt varchar(100) NOT NULL,
  is_active tinyint(1) NOT NULL DEFAULT 1,
  last_login_date datetime DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX email_UNIQUE (email)
)
ENGINE = INNODB
AUTO_INCREMENT = 25
AVG_ROW_LENGTH = 682
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

work_order_detail_has_technician *CANNOT CREATE FKs*

Code: Select all

USE my_db;
CREATE TABLE my_db.work_order_detail_has_technician (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  work_order_detail_id int(10) NOT NULL,
  assigned_by_user_id int(10) NOT NULL,
  technician_id int(10) NOT NULL,
  inserted_date datetime NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

work_order_detail_has_work_order_status *THIS ONE WORKS*

Code: Select all

USE my_db;
CREATE TABLE my_db.work_order_detail_has_work_order_status (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  work_order_detail_id int(10) UNSIGNED NOT NULL,
  work_order_status_id int(10) UNSIGNED NOT NULL,
  inserted_date datetime NOT NULL,
  user_id int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_work_order_detail_has_work_order_status_work_order_detai_idx (work_order_detail_id),
  INDEX fk_work_order_detail_has_work_order_status_work_order_statu_idx (work_order_status_id),
  CONSTRAINT FK_work_order_detail_has_work_order_status_work_order_detail_id FOREIGN KEY (work_order_detail_id)
  REFERENCES my_db.work_order_detail (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_work_order_detail_has_work_order_status_work_order_status_id FOREIGN KEY (work_order_status_id)
  REFERENCES my_db.work_order_status (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 8
AVG_ROW_LENGTH = 2340
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Currently running the Professional Version (trial), version 6.0.189.

So from the db diagram, I am trying to create a FK between work_order_detail_has_technician.assigned_by_user_id and user.id, or any other table. It seems to break when creating any foreign key, not just on the user table. Upon dragging from assigned_by_user_id and user.id, I get the popup and press "OK" and immediately get the message, "Can't create table `my_db.#sql-4a1_12f` (errno: 150)"

HOWEVER, if I drag from work_order_detail_has_work_order_status.user_id to user.id. I get the popup, click "OK" and it creates the FK constraint and the Index.

Any ideas?

[blyxx86]

Wait.. I see that those columns weren't marked as UNSIGNED.

Once I changed them to be unsigned, then the FK and Index created successfully.

[alexa]

This issue appears to be not connected with our product, but lies in the MySQL server itself. Please also see the following article: http://stackoverflow.com/questions/1085 ... -errno-150