Recently I was working on an unrelated issue and I noticed that there was no foreign key constraint on a relationship that I thought obviously should have one.
I decided to investigate the database migrations for both of these tables to see if this was just an oversight or something different. For this post, let’s call the two tables “test” and “test_reference”
First I looked at all the revisions for the table “test”. Low and behold the fk constraint was there when the table was created:
create table test (id serial8 not null, test_reference_id int8, PRIMARY KEY(id)); alter table test add constraint foreign key (test_reference_id) references test_reference constraint fk_test_reference;
Turns out there was no other db migration that dropped this foreign key constraint. So what happened to it?
Next I took a look at all the revisions for the referred to table “test_reference”. I found this incriminating revision:
ALTER TABLE test_reference MODIFY (id serial8 NOT NULL); ALTER TABLE test_reference ADD CONSTRAINT PRIMARY KEY(id);
We changed the primary key!
Informix had no complaint on this…but under the covers we lost the foreign key references to our table.
I think sometimes we take for granted these database changes but this is a good example why it pays to be careful on these type of updates.