MySql import error

Report Bugs
1

I tried to import my db in mysql and it gives the errno: 150 "Foreign key constraint is incorrectly formed" error.

image
image992×285 8.15 KB

I changed nothing to the sql file and all the databases are created but it crashes once it arrives at the references.

What do i do?

2

Hi Sander_Brilman
Thanks for letting us know,
Can you give us the sql or dbml code that fails?
Thanks.

3

Thanks for replying!

Here are both the dbml and the mysql it generates.

dbml:

Table users {
  user_id int [not null]
  name varchar(255) [not null]
  email varchar(255) [not null, unique]
  password varchar(255) [not null]
  tel varchar(20) [not null]
  paid tinyint(1) [NOT NULL, default: 0, Note: '0 = not paid, 1 = paid']
}

Table bab {
  id int [pk, increment]
  title varchar(40) [NOT NULL]
  short_description varchar(150) [NOT NULL]
  description text [NOT NULL]
  
  city varchar(255) [NOT NULL]
  address varchar(255) [NOT NULL]
  post_code varchar(255) [NOT NULL]
  
  capacity tinyint [NOT NULL]
  price smallint [NOT NULL]
  
  website varchar(255) [note: 'paid only']
  
  Note: 'bed and breakfast table'
}

Table rooms {
  bab_id int [NOT NULL]
  room_id int [pk, increment]
  
  title varchar(255) [NOT NULL]
  description text [NOT NULL]
  
  capacity tinyint [NOT NULL]
  price smallint [NOT NULL]
  
  Note: 'images for paid only'
}

table images {
  image_id int [pk, increment]
  alt VARCHAR(255) [NOT NULL, default: 'Foto']
  path varchar(255) [NOT NULL]
}

table reviews {
    bab_id int [NOT NULL]
    id int [pk, increment]
    
    text text [NOT NULL]
    hidden_text text
    
    stars tinyint [NOT NULL]
}

table reactions {
    reaction_id int [pk, increment, NOT NULL]
    review_id int [NOT NULL]
    text text [NOT NULL]
}


Ref: "reactions"."review_id" - "reviews"."id"

Ref: "rooms"."room_id" < "images"."image_id"

Ref: "bab"."id" < "images"."image_id"

Ref: "bab"."id" < "reviews"."bab_id"

Ref: "bab"."id" < "rooms"."bab_id"

Ref: "bab"."id" - "users"."user_id"

MySql:

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) UNIQUE NOT NULL,
  `password` varchar(255) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `paid` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = not paid, 1 = paid'
);

CREATE TABLE `bab` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(40) NOT NULL,
  `short_description` varchar(150) NOT NULL,
  `description` text NOT NULL,
  `city` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `post_code` varchar(255) NOT NULL,
  `capacity` tinyint NOT NULL,
  `price` smallint NOT NULL,
  `website` varchar(255) COMMENT 'paid only'
);

CREATE TABLE `rooms` (
  `bab_id` int NOT NULL,
  `room_id` int PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `capacity` tinyint NOT NULL,
  `price` smallint NOT NULL
);

CREATE TABLE `images` (
  `image_id` int PRIMARY KEY AUTO_INCREMENT,
  `alt` VARCHAR(255) NOT NULL DEFAULT "Foto",
  `path` varchar(255) NOT NULL
);

CREATE TABLE `reviews` (
  `bab_id` int NOT NULL,
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `text` text NOT NULL,
  `hidden_text` text,
  `stars` tinyint NOT NULL
);

CREATE TABLE `reactions` (
  `reaction_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `review_id` int NOT NULL,
  `text` text NOT NULL
);

ALTER TABLE `bab` COMMENT = 'bed and breakfast table';

ALTER TABLE `rooms` COMMENT = 'images for paid only';

ALTER TABLE `reviews` ADD FOREIGN KEY (`id`) REFERENCES `reactions` (`review_id`);

ALTER TABLE `images` ADD FOREIGN KEY (`image_id`) REFERENCES `rooms` (`room_id`);

ALTER TABLE `images` ADD FOREIGN KEY (`image_id`) REFERENCES `bab` (`id`);

ALTER TABLE `reviews` ADD FOREIGN KEY (`bab_id`) REFERENCES `bab` (`id`);

ALTER TABLE `rooms` ADD FOREIGN KEY (`bab_id`) REFERENCES `bab` (`id`);

ALTER TABLE `users` ADD FOREIGN KEY (`user_id`) REFERENCES `bab` (`id`);

The mysql is straight from the export, i did not change it.

4

Hi Sander_Brilman,
I think you should create unique constraint for review_id field of reaction table. It helps to ensure the integrity of the data. A UNIQUE constraint, for instance, makes sure that there is at most one instance of any possible value (or tuple, in the case of a composite constraint) in a table.
You can try:

Table users {
  user_id int [not null]
  name varchar(255) [not null]
  email varchar(255) [not null, unique]
  password varchar(255) [not null]
  tel varchar(20) [not null]
  paid tinyint(1) [NOT NULL, default: 0, Note: '0 = not paid, 1 = paid']
}

Table bab {
  id int [pk, increment]
  title varchar(40) [NOT NULL]
  short_description varchar(150) [NOT NULL]
  description text [NOT NULL]
  
  city varchar(255) [NOT NULL]
  address varchar(255) [NOT NULL]
  post_code varchar(255) [NOT NULL]
  
  capacity tinyint [NOT NULL]
  price smallint [NOT NULL]
  
  website varchar(255) [note: 'paid only']
  
  Note: 'bed and breakfast table'
}

Table rooms {
  bab_id int [NOT NULL]
  room_id int [pk, increment]
  
  title varchar(255) [NOT NULL]
  description text [NOT NULL]
  
  capacity tinyint [NOT NULL]
  price smallint [NOT NULL]
  
  Note: 'images for paid only'
}

table images {
  image_id int [pk, increment]
  alt VARCHAR(255) [NOT NULL, default: 'Foto']
  path varchar(255) [NOT NULL]
}

table reviews {
    bab_id int [NOT NULL]
    id int [pk, increment]
    
    text text [NOT NULL]
    hidden_text text
    
    stars tinyint [NOT NULL]
}

table reactions {
    reaction_id int [pk, increment, NOT NULL]
    review_id int [NOT NULL, unique]
    text text [NOT NULL]
}


Ref: "reactions"."review_id" - "reviews"."id"

Ref: "rooms"."room_id" < "images"."image_id"

Ref: "bab"."id" < "images"."image_id"

Ref: "bab"."id" < "reviews"."bab_id"

Ref: "bab"."id" < "rooms"."bab_id"

Ref: "bab"."id" - "users"."user_id"

Thanks

5

Ah i see, So the foreign key of a 1 to 1 relationship must have the unique constraint. Thanks for your help!

1 Like