Unable to create foreign keys with innodb phpmyadmin/adminer

Hosting Support
1

My site

###sql :

ALTER TABLE news ADD CONSTRAINT fk_dept_id FOREIGN KEY (author) REFERENCES users(id)

###Error:

Cannot add or update a child row: a foreign key constraint fails (********.#sql-184a_23f90, CONSTRAINT fk_dept_id FOREIGN KEY (author) REFERENCES users (id))

@Admin
@moderators

thanks for help

3

Please try using adminer

2 Likes
4

Adminer also not working

Can anyone help me please

5

Try to remove CONSTRAINT fk_dept_id from your query and see if it works. If it still doesn’t you might have added the foreign key before; that’s why it won’t let you add it again.

1 Like
6

Thanks for help

@Admin
@moderators

7

I really don’t think it is.

The a foreign key constraint fails is not a “hosting problem”, it means that MySQL cannot add the foreign key constraint because the data is invalid.

I had a look at your database. I see you have a few records in the news table already, each with the author column set. However, the users table is empty. So you’re trying to add a foreign key from news.author to user.id, but all authors in the database do not have matching users.

To fix this, you’ll first need to populate the users table with the right records, update the author columns in the news table so the user IDs match, and only then you can add the foreign key constraint.

Alternatively, you can run SET FOREIGN_KEY_CHECKS=0; before adding the foreign key so MySQL won’t warn you about the corrupted data. But then you’ll still have corrupted data.

3 Likes
8

Thanks for your help

I tried that but doesn’t worked.

it WORKED :blush:
sorry :pensive: for your time

####Please don’t remove this topic####

Maybe helpful for others

1 Like
9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.