MySQL insert statement fails with foreign key constraint

Question

I have this statement:

INSERT INTO `alias`( `alias`, `ref_links_id`) VALUES ("3334",4)

And I get this error:

Cannot add or update a child row: a foreign key constraint fails 

(`bestr_main`.`alias`, CONSTRAINT `alias_ibfk_1` FOREIGN KEY (`ref_links_id`) 

REFERENCES `links` (`link_id`) ON DELETE CASCADE ON UPDATE CASCADE)

The alias table is connected to link table with a foreign key. Why do I get this error when inserting a record?

I see now.. I tried to change the link between the keys to another table and I get this:

  1452 - Cannot add or update a child row: a foreign key constraint fails 

  (`bestr_main`.<result 2 when explaining filename '#sql-73c_38e0'>, CONSTRAINT 

 `#sql-73c_38e0_ibfk_1` FOREIGN KEY (`ref_links_id`) REFERENCES `refs` (`ref_id`) 

 ON DELETE CASCADE ON UPDATE C)

What does that say?

Please, add which foriegn key is avialable in primary key table, Otherwise remove foriegn key from table... — Vikram Jain
– Vikram Jain, Commented Oct 9, 2012 at 10:18
Please start inserting values from the parent table which has referred , because of the parent table does'nt exist with the values it may results with above mentioned errors. — thar45
– thar45, Commented Oct 9, 2012 at 10:33

xkeshav · Accepted Answer · 2012-10-09 10:17:21Z

2

Read the error

FOREIGN KEY (ref_links_id) REFERENCES links (link_id))

Means

`links`.link_id  (Parent)

`alias`.ref_links_id (Child)

and no child exist without parent. so first check parent table for the value which you are inserting into child table

answered Oct 9, 2012 at 10:17

xkeshav

54.2k47 gold badges183 silver badges256 bronze badges

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

Comments

Raphaël Althaus · Accepted Answer · 2012-10-09 10:15:49Z

1

You need to have a link_id with value 4 in your links table, if you wanna insert a 4 in alias.ref_links_id.

Create it in the links table first if it doesn't exist.

answered Oct 9, 2012 at 10:15

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Comments

Prasath Albert · Accepted Answer · 2012-10-09 10:16:43Z

1

the link table doesn't have the value '4'. Please check the link table value, that it has the value '4'.

answered Oct 9, 2012 at 10:16

Prasath Albert

1,45710 silver badges20 bronze badges

Comments

Vikram Jain · Accepted Answer · 2012-10-09 10:20:25Z

1

if((select count(*) from primaty_table where pk_id=4) > 0)
{
INSERT INTO `alias`( `alias`, `ref_links_id`) VALUES ("3334",4)
}

answered Oct 9, 2012 at 10:20

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

Phlak3 · Accepted Answer · 2022-05-17 16:49:38Z

1

Maybe someone will find this, my problem was that in my MySQL script I had Engine = MyISAM; instead of InnoDB. changed Engine = InnoDB; and it worked.

answered May 17, 2022 at 16:49

Phlak3

111 bronze badge

Comments

iLaYa ツ · Accepted Answer · 2012-10-09 10:20:50Z

-1

ref_links_id 4 will be already exist in the table alias. You can not insert duplicate value for foreign key constraint.

If you want to delete foreign key,

ALTER TABLE `alias` DROP FOREIGN KEY ref_links_id;

Then try INSERT INTOalias(alias,ref_links_id) VALUES ("3334",4)

answered Oct 9, 2012 at 10:20

iLaYa ツ

4,0173 gold badges34 silver badges51 bronze badges

Collectives™ on Stack Overflow

MySQL insert statement fails with foreign key constraint

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related