Why it links to old table rather than the new one

Hi,

Here is the scenario: I created a new table, foo and there was an old one also called foo . So I rename the old one to foo_old through rename before I created the new foo. This was a success. But I found out a problem that my other tables that have foreign key constraint with table foo become constrained with foo_old instead of foo. My expectation is that the constrain is still foo not foo_old. How could I fix this? Thanks in advance!

Hi @swdaily,

When you renamed the original table foo to foo_old, the foreign key constraints correctly changed the referenced table name as well to foo_old:

> create table foo (id int PRIMARY KEY, name string);
> create table child (child_id int PRIMARY KEY, foo_id int not null references foo(id), child_name string);

> select table_name, constraint_name, referenced_table_name from information_schema.referential_constraints;
  table_name |  constraint_name  | referenced_table_name
+------------+-------------------+-----------------------+
  child      | fk_foo_id_ref_foo | foo

> alter table foo rename to foo_old;

> select table_name, constraint_name, referenced_table_name from information_schema.referential_constraints;
  table_name |  constraint_name  | referenced_table_name
+------------+-------------------+-----------------------+
  child      | fk_foo_id_ref_foo | foo_old

To change the referenced_table_name to point to the new foo table, you will need to

  • use DROP CONSTRAINT to drop existing constraints that point to table foo_old, and
  • use ADD CONSTRAINT to add constraints that point to the new table foo.

Hope this info helps!

Regards,
Florence
Technical Support Engineer