Sql Query Continue on Foreign Key Error

SQL Query not working Foreign key constraint is incorrectly formed

Author: Salvatrix Ngải Date: 2022-10-12

All you need to know about SQL Query not working Foreign key constraint is incorrectly formed , in addintion to php - How to define FOREIGN KEY constraint in mysql in my case not working , mysql - Why is this foreign key constraint incorrectly formed? , sql - mysql Foreign key constraint is incorrectly formed error , sql - Can not delete or modify or see same table foreign key constraint

• SQL Query not working Foreign key constraint is incorrectly formed
• php - How to define FOREIGN KEY constraint in mysql in my case not working
• mysql - Why is this foreign key constraint incorrectly formed?
• sql - mysql Foreign key constraint is incorrectly formed error
• sql - Can not delete or modify or see same table foreign key constraint

SQL Query not working Foreign key constraint is incorrectly formed

Question:

Can someone please explain why this SQL Query isn't working for me? I get this error: Foreign key constraint is incorrectly formed

                      DROP DATABASE IF EXISTS foodblog;  CREATE DATABASE foodblog;  USE foodblog;  CREATE TABLE posts (     id int(11) AUTO_INCREMENT,     titel varchar(255),     datum DATETIME DEFAULT current_timestamp(),     img_url varchar(255),     inhoud text,     auteur_id int(11),     PRIMARY KEY (id),     FOREIGN KEY (auteur_id) REFERENCES auteurs(id) );  CREATE TABLE auteurs (     id int(11) AUTO_INCREMENT,     auteur varchar(255),     PRIMARY KEY (id) );                                          

Solution 1:

Try re-ordering the queries. Because, when first query executes, the table auteurs would not be available. Here is the corrected code:

                      DROP DATABASE IF EXISTS foodblog;  CREATE DATABASE foodblog;  USE foodblog;  CREATE TABLE auteurs (     id int(11) AUTO_INCREMENT,     auteur varchar(255),     PRIMARY KEY (id) );  CREATE TABLE posts (     id int(11) AUTO_INCREMENT,     titel varchar(255),     datum DATETIME DEFAULT current_timestamp(),     img_url varchar(255),     inhoud text,     auteur_id int(11),     PRIMARY KEY (id),     FOREIGN KEY (auteur_id) REFERENCES auteurs(id) );                                          

php - How to define FOREIGN KEY constraint in mysql in my case not working

Question:

I need to know about how to declare foreign key in mysql and how it works. Here's one sample first table contains name, age the second table refer the first tables name. While I run this, I receive error only.

                        <?php $conn=new mysqli("localhost","root","12345"); $sql="USE new"; $conn->query($sql); $sql="CREATE TABLE test(name varchar(20),age integer)"; $conn->query($sql); $sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test     (name)"; if($conn->query($sql)==true) {     header('Locaton:test3.html'); } else {     echo "error"; } ?>                                              

Can anyone help me?

Solution 1:

You are missing a ) parenthesis at the end

                        $sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test (name))";                                              

Solution 2:

foreign key must refrence from a Primary key then use a primary key on name,

                        $sql="CREATE TABLE test(name varchar(20) PRIMARY KEY,age integer)";                                              

and also change on

                        $sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test(name))";                                              

if your name field have chance to duplicate then take one another field id and use it to reference.

Solution 3:

Please note that you must use InnoDB when using foreign keys. Unless you have InnoDB as default, you need to specify the table type when creating the table.

                        CREATE TABLE test(name varchar(20) primary key,age integer) engine=innodb; CREATE TABLE test2(name varchar(10) primary key,FOREIGN KEY (name) REFERENCES test     (name)) engine=innodb;                                              

Also note that the you foreign keys need to be referencing indexed columns. Using primary key is one way to achieve this.

mysql - Why is this foreign key constraint incorrectly formed?

Question:

How I arrived at this question is a spaghetti of error messages in itself.

First, it was error message 1452: Cannot add or update a child row: a foreign key constraint fails.

...from trying to insert data with an absolutely valid foreign key.

Now I've re-created the tables in order to duplicate the problem and found a new error, which brings me closer to the root cause.

                          Error Code: 1005. Can't create table `covers` (errno: 150 "Foreign key constraint is incorrectly formed")                                                  

Here are the tables:

                          CREATE TABLE IF NOT EXISTS `entities` (   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,   `type` VARCHAR(255) NOT NULL,   PRIMARY KEY (`id`)  ) ENGINE = InnoDB AUTO_INCREMENT = 100001 PARTITION BY KEY() PARTITIONS 10 ;  CREATE TABLE IF NOT EXISTS `covers` (   `id` BIGINT(20) NOT NULL,   `title` VARCHAR(255) NOT NULL,   PRIMARY KEY (`id`)  ,   CONSTRAINT `covers-id`     FOREIGN KEY (`id`)     REFERENCES `entities` (`id`)     ON DELETE CASCADE     ON UPDATE CASCADE) ENGINE = InnoDB ;                                                  

Its a new database designed in Workbench. The Forward Engineer script created the tables without error, but it seems there is a problem with them.

Adding the output from show engine innodb status

                          ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2016-06-21 17:42:46 7f7ffa000700 Error in foreign key constraint of table `baka`.`IF`: Create  table `baka`.`IF` with foreign key constraint failed. Referenced table `baka`.`entities` not found in the data dictionary near '     FOREIGN KEY (`id`)     REFERENCES `entities` (`id`)     ON DELETE CASCADE     ON UPDATE CASCADE) ENGINE = InnoDB'.                                                  

Solution 1:

I had the same problem. The source of mine was that the tables didn't have the same engine. So my solution was to put the same engine for both tables.

Solution 2:

foreign key must refrence from a Primary key then use a primary key on name,

                          $sql="CREATE TABLE test(name varchar(20) PRIMARY KEY,age integer)";                                                  

and also change on

                          $sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test(name))";                                                  

if your name field have chance to duplicate then take one another field id and use it to reference.

Solution 3:

Same problem here. The error was that the dump file i was restoring from had the tables in the wrong order. So by manually creating the tables referenced to first solved the problem.

sql - mysql Foreign key constraint is incorrectly formed error

Question:

I have two tables, table1 is the parent table with a column ID and table2 with a column IDFromTable1 (not the actual name) when I put a FK on IDFromTable1 to ID in table1 I get the error Foreign key constraint is incorrectly formed error. I would like to delete table 2 record if table1 record gets deleted. Thanks for any help

                            ALTER TABLE `table2`      ADD CONSTRAINT `FK1`        FOREIGN KEY (`IDFromTable1`) REFERENCES `table1` (`ID`)        ON UPDATE CASCADE        ON DELETE CASCADE;                                                      

Let me know if any other information is needed. I am new to mysql

Solution 1:

I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length.

The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them both the same exact type, the foreign key creation worked perfectly.

Solution 2:

For anyone facing this problem, just run SHOW ENGINE INNODB STATUS and see the LATEST FOREIGN KEY ERROR section for details.

Solution 3:

I had the same problem when the parent table was created using MyISAM engine. It's a silly mistake, which I fixed with:

                            ALTER TABLE parent_table ENGINE=InnoDB;                                                      

sql - Can not delete or modify or see same table foreign key constraint

Question:

A same table foreign key constraint in my database is not accessible. I can not drop it, disable it, add it back, ... How do I remove it and re-add it?

Note: I have several versions of my database all created with the same script. Only in one I see this behavior. In others, this key is easily added and removed.

Many thanks. Here is some scripts I ran and the result:

At some point in the past i ran the following script:

                              ALTER TABLE Recipe ADD CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id FOREIGN KEY (DuplicateOfRecipeId)     REFERENCES Recipe (Id) ;                                                          

now running

                              ALTER TABLE Recipe DROP CONSTRAINT  FK_Recipe_DuplicateOfRecipeId_Recipe_Id  results in the following error: 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id' is not a constraint.                                                          

and running

                              ALTER TABLE Recipe NOCHECK CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id results in: Constraint 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id' does not exist.                                                          

so i run

                              alter table Recipe ADD CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id FOREIGN KEY (DuplicateOfRecipeId) REFERENCES Recipe (Id);                                                          

and i get:

                              The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Recipe_DuplicateOfRecipeId_Recipe_Id". The conflict occurred in database "CrawlerDB", table "dbo.Recipe", column 'Id'.                                                          

so I run:

                              select COUNT(*) from sys.objects where name = 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id' select COUNT(*) from sys.all_objects where name = 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id' SELECT COUNT(*) FROM sys.foreign_keys where name = 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id'                                                          

and all 3 return nothing.

Whats going on and how do I fix it? I need to access this object, remove it and add it back.
Many thanks!

Solution 1:

I'm guessing that your master database is corrupted. You'd probably be best suited by rebuilding it.

However, as a workaround, you could try this:

1. Duplicate your foreign key into a non-FK column

   ALTER TABLE Recipe ADD DuplicateOfFK INT

2. Copy all your FK data to the duplicate

   UPDATE Recipe SET DuplicateOfFK = DuplicateOfRecipeId

3. Drop the Foreign Key column

   ALTER TABLE Recipe DROP COLUMN DuplicateOfRecipeId

4. Go backwards.

   ALTER TABLE Recipe ADD DuplicateOfRecipeId INT

   UPDATE Recipe SET DuplicateOfRecipeId = DuplicateOfFK

   ALTER TABLE Recipe DROP COLUMN DuplicateOfFK

5. Add the constraint back.

Solution 2:

Your comment above should have given you the clue:

after setting all values in the foreign key field to null, then everything works again. But why?

Unfortunately the error message can be misleading. The statement adding the foreign key back failed not because you were creating a circular reference, but because DuplicateOfRecipeId contained at least one non-null value that was not already contained in Id.

By that I mean that the following code works:

                              create table dbo.Recipe (     Id int not null identity(1, 1)         constraint PK_Recipe primary key nonclustered     , DuplicateOfRecipeId int not null ); insert dbo.Recipe values (1), (2), (3); alter table Recipe add constraint FK_Recipe_DuplicateOfRecipeId_Recipe_Id foreign key (DuplicateOfRecipeId) references Recipe (Id); drop table dbo.Recipe go                                                          

But the following code fails (note the different DuplicateOfRecipeId values):

                              create table dbo.Recipe (     Id int not null identity(1, 1)         constraint PK_Recipe primary key nonclustered     , DuplicateOfRecipeId int not null ); insert dbo.Recipe values (4), (5), (6); alter table Recipe add constraint FK_Recipe_DuplicateOfRecipeId_Recipe_Id foreign key (DuplicateOfRecipeId) references Recipe (Id); drop table dbo.Recipe go                                                          

acevedohoullich.blogspot.com

Source: https://sqlerrors.com/howto/45818/SQL-Query-not-working-Foreign-key-constraint-is-incorrectly-formed

Share this post