Getting 1005 constraint error on create table

Question

I am making a script that reading some php files creates a SQL file. I have almost finished, but I am getting a MySQL error that I can't figure it out :(

This is the SQL code:

CREATE TABLE `tag` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Id único de cada tag',
  `name` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Nombre de la tag',
  `id_user` INT(11) NOT NULL COMMENT 'Id del usuario',
  `created_at` DATETIME NOT NULL COMMENT 'Fecha de creación del registro',
  `updated_at` DATETIME NULL COMMENT 'Fecha de última modificación del registro',
  PRIMARY KEY (`id`),
  KEY `fk_tag_user_idx` (`id_user`),
  CONSTRAINT `fk_tag_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Id único de un usuario',
  `user` VARCHAR(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Nombre de usuario',
  `pass` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Contraseña del usuario',
  `num_photos` INT(11) NOT NULL DEFAULT '0' COMMENT 'Número de fotos de un usuario',
  `score` FLOAT NOT NULL DEFAULT '0' COMMENT 'Puntuación del usuario',
  `active` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Usuario activo 1 o no 0',
  `last_login` DATETIME NOT NULL COMMENT 'Fecha de la última vez que inició sesión',
  `notes` TEXT NOT NULL DEFAULT '' COMMENT 'Notas sobre el usuario',
  `created_at` DATETIME NOT NULL COMMENT 'Fecha de creación del registro',
  `updated_at` DATETIME NULL COMMENT 'Fecha de última modificación del registro',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are more tables, but these are the first two and it already crashes with:

#1005: Can't create table `tag` (Error: 150)

The script starts with SET FOREIGN_KEY_CHECKS = 0; and ends with SET FOREIGN_KEY_CHECKS = 1;

Any help would be appreciated, thanks!!

Create the user table first. It has to exist so the other table can refer to it — juergen d
– juergen d, Commented Nov 21, 2018 at 10:53

Mayank Pandeyz · Accepted Answer · 2018-11-21 10:57:55Z

2

I think the issue is of ordering of query, look into this line:

KEY `fk_tag_user_idx` (`id_user`),
  CONSTRAINT `fk_tag_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

here you are referring to the table user, which is not created so far as the query to create user is below and which runs after first query.

So create the user table first and then tag table.

answered Nov 21, 2018 at 10:57

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iñigo Gorosabel Over a year ago

Thanks, that was it... I thought setting foreign key check to 0 disabled ALL relation checks and as the table user was created inmediately after when I set it back to 1 it would all work. Thanks!!

Bingen · Accepted Answer · 2018-11-21 11:10:50Z

1

As Said, create user table first, or, if it's a problem, create all the tables without Foreign Key constraints and update them after all the create tables

answered Nov 21, 2018 at 11:10

Bingen

295 bronze badges

Collectives™ on Stack Overflow

Getting 1005 constraint error on create table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related