error while creating table with Foreign key mysql

Asked

Viewed 371 times

0

You’re making a mistake Can't create table when creating this table in Mysql. I have no idea where problem D is:

CREATE TABLE IF NOT EXISTS `tbnoticiasrel` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`id_noticia` bigint(20) NOT NULL,
`id_noticia_relacionada` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
 FOREIGN KEY fk_id_noticia(`id_noticia`) 
 REFERENCES tbnews(`id`),
 FOREIGN KEY fk_id_noticia_relacionada(`id_noticia_relacionada`) ,
 REFERENCES tbnews(`id`)
 )
php mysql

  • you already have the fk_id_noticia_related table created?

  • Try FOREIGN KEY (\id_noticia`) REFERENCES ...` without the FK name.

  • fk_id_news_related is the name I’m giving to my FK -

  • Which error message the bank shows you?

  • gave the same error this way Kaduamaral

  • shows Cant 'create table'nameserver'. 'nameserver' (Errno: 150)

  • 2

    puts SET FOREIGN_KEY_CHECKS=0; on the line before the CREATE TABLE IF NOT EXISTS tbnoticiasrel with that he will not check the FOREIGN KEY

  • with this SET FOREIGN_KEY_CHECKS=0; worked, thanks (:

  • @Estaciodifabio tried to execute the code without the virgula that I mentioned? That one SET FOREING_KEY... seems to be a Alternative Solution.

Show 4 more comments

3 answers

1


set FOREIGN_KEY_CHECKS=0; on the line before the CREATE TABLE IF NOT EXISTS tbnoticiasrel with that he will not check the FOREIGN KEY

1

The problem is in the syntax of the second FK, has a comma between the FK and the Reference:

 FOREIGN KEY fk_id_noticia(`id_noticia`) 
 REFERENCES tbnews(`id`),
 FOREIGN KEY fk_id_noticia_relacionada(`id_noticia_relacionada`) ,
 REFERENCES tbnews(`id`)

Just remove it:

 FOREIGN KEY fk_id_noticia(`id_noticia`) 
 REFERENCES tbnews(`id`),
 FOREIGN KEY fk_id_noticia_relacionada(`id_noticia_relacionada`)
 REFERENCES tbnews(`id`)

0

Whenever you create a referenced table for a database "with data", by default, Mysql, does a check of the referenced indexes, when it does not find the reference within the table usually gives this kind of problem, to solve this in a way that does not need to use the SET FOREIGN_KEY_CHECKS=0;, whose command is used to block the reindexation of foreign keys, make a backup of the data of the referenced tables by creating a copy with a underline, for example, at the end of your _backup table, then in the original tables, delete all the data, create the new table that references and reimport the data of these tables to the original tables, so all the content will be reindexed, so that there are no future conflicts that can halt your system.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.