Toad World® Forums

Altering a table that is referenced by FK results in error.

Steps to reproduce:

  1. Create table A with identity column GroupId
  2. Create table B with identity column ItemId and GroupId with FK to A.GroupId with cascade
  3. Alter table A and add a new column.
    Press Ok.

The generated script gives an error on its last line

DROP TABLE [Admin].[tmp_8460ef8c7e3c4018b3b1580c83726ff7]

about not being able to drop table because FK references exist because table B actually is referencing the tmp table as its foreign key since near the top of the script the table is renamed in prep for a rebuild:

EXECUTE [sp_rename]
@objname = N’[dbo].[A]’,
@newname = N’tmp_8460ef8c7e3c4018b3b1580c83726ff7’,
@objtype = ‘OBJECT’;


The script be enhanced to re-reference FKs away from the tmp file and to the new table before the final delete of tmp table at the end of the script.

Thanks for your consideration!

Message was edited by: chris.rokusek_218


Thanks for report. I have a feeling we have addressed similar issue in the past.
Are you using 5.7 beta or the issue is related to one of prev.release (please, specify which one).