MySQL Foreign Key – Adding a Foreign Key Constraint Without Losing Data

foreign keyMySQLmysql-5.6

I have one table called users and another table called teachers (the users table has data in it).

I want to add a column to teachers with a foreign key constraint making a reference to a column named id in the users table.

How can I do that without losing the rows contained in the users table?

Best Answer

Your comment (quote): "the teacher's table has data on it and I want to add a new column that is a foreign key to the user's table the column is not null I don't want it to be nullable when I run the query I get a foreign key constraint error" ... seems to indicate that your situation looks a bit like this (MySQL 5.7): 

-- both users and teachers contain data ...
create table users (
  userid integer primary key
);

create table teachers(
  teacherid integer primary key
);

insert into users values (1),(2),(3),(4),(5);

insert into teachers values (10),(11),(12),(13),(14);

Adding a column and a NOT NULL constraint to the TEACHERS table works ... MySQL just sets the userids to 0 (!) - as they cannot be NULL (This step should not be possible e.g. a Postgresql server or Oracle would laugh in our face if we tried this.)

alter table teachers 
add userid integer not null;

mysql> select * from teachers;
+-----------+--------+
| teacherid | userid |
+-----------+--------+
|        10 |      0 |
|        11 |      0 |
|        12 |      0 |
|        13 |      0 |
|        14 |      0 |
+-----------+--------+
5 rows in set (0.00 sec)

-- Do we really have a NOT NULL constraint now (userid)?  Yes.
mysql> insert into teachers values (16, NULL);
ERROR 1048 (23000): Column 'userid' cannot be null

Now, lets add a foreign key constraint. This fails, as all userids in TEACHERS are 0, and there is no user with userid 0 in USERS. (I think that this is the step that is giving you grief.)

mysql> alter table teachers
    -> add constraint theforeignkey
    -> foreign key (userid) references users(userid);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-554_4`, CONSTRAINT `theforeignkey` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`))

Now - we could do something like the next couple of steps. I DO NOT RECOMMEND THIS for real life settings as it involves using incorrect data.

MySQL allowed the teachers.userid to be 0 (which is incorrect). If we use a valid userid instead, we can subsequently apply the foreign key constraint. CAVEAT: the UPDATE will (most likely) use incorrect values. 

mysql> update teachers
    -> set userid = 1 ;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0


-- works now
mysql> alter table teachers
    -> add constraint theforeignkey
    -> foreign key (userid) references users(userid);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Are both the NOT NULL and the FOREIGN KEY in place now? Yes.

mysql> insert into teachers values (16, NULL);
ERROR 1048 (23000): Column 'userid' cannot be null

mysql> insert into teachers values (16, 1000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`teachers`, CONSTRAINT `theforeignkey` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`))

mysql> insert into teachers values (16, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from teachers;
+-----------+--------+
| teacherid | userid |
+-----------+--------+
|        10 |      1 |
|        11 |      1 |
|        12 |      1 |
|        13 |      1 |
|        14 |      1 |
|        16 |      2 |
+-----------+--------+
6 rows in set (0.00 sec)

CAUTION: now you need to check (and amend if necessary) all the mappings teacherid <-> userid (in the TEACHERS table)! You are on thin ice when running the update. It would be better to use correct DDL code first, and then insert correct data right from the start.

Related Question