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

Question

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`))

foreign keys are intended to enforce parent-child relationships in data. If you have dangling records then you shouldn't be using foreign keys until you've repaired the broken relationships. Doing so otherwise basically goes against what foreign keys are for. "I have an apple, is there any way to make it NOT be an apple?" — Marc B, Oct 23 '13 at 19:15
You could add a valid default value, then update as necessary. Not exactly a good way to go. — Jaime Torres, Oct 23 '13 at 19:15
That being said, you *CAN* temporarily disable FK enforcement, but this is normally used only in certain special circumstances (e.g. loading a dump/backup file). In normal usage it is NOT a good idea to disable them. See http://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql — Marc B, Oct 23 '13 at 19:16
This may or may not help, but when I have had to do this using SQL Server Management Studio I add the new Foreign Key as a nullable value, run an update query to populate the records with their key value, then switch the foreign key to be "not null". — Scope Creep, Oct 23 '13 at 19:17

score 6 · Accepted Answer · answered Oct 23 '13 at 19:16

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.

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

1 Answers1