CODEWITHSUNDEEP
mysqlsql
Pavel
Pavel

Reputation: 1288

is there a way to add a foreign key when there is already some data in the tables?

I have these two tables and some data already in them.

CREATE TABLE `system_user_data` (
`id_user` bigint(20) NOT NULL AUTO_INCREMENT,
`user_login` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8;

CREATE TABLE `system_user_tokens` (
`id_token` bigint(20) NOT NULL AUTO_INCREMENT,
`token_user` bigint(20) DEFAULT NULL,
`token_token` varchar(20) DEFAULT NULL,
`token_createdate` date DEFAULT NULL,
   PRIMARY KEY (`id_token`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

When I try to add a foreign key token_user=id_user I get an error. It is working when there is not any data.

alter table system_user_tokens add foreign key (token_user) references system_user_data (id_user);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`name`.`#sql-1b44_727`, CONSTRAINT `#sql-1b44_727_ibfk_1` FOREIGN KEY (`token_user`) REFERENCES `system_user_data` (`id_user`))

Upvotes: 2

Views: 3630

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726849

Yes, it is possible to add a foreign key constraint, and yes, you are doing it right.

The problem is that in order for adding a foreign key constraint to succeed, the data in the table must already meet that constraint, i.e. all rows of your system_user_tokens.token_user values must be present in system_user_data.id_user. Currently, this is not true for one or more rows of your system_user_tokens table.

Run this query to find all violations of the foreign key that you are trying to add:

SELECT *
FROM system_user_tokens tok
WHERE NOT EXISTS (
    SELECT * FROM system_user_data u WHERE tok.token_user = u.id_user
)

Analyze the rows returned by this query, fix all violations, and run your alter command again; it should succeed.

Upvotes: 7

Related Questions