|
Replies:
1
-
Pages:
1
-
Last Post:
Apr 16, 2012 4:09 AM
by: Valentine
|
|
|
Posts:
3
Registered:
3/22/12
|
|
|
|
Altering a table that is referenced by FK results in error.
Posted:
Apr 12, 2012 11:20 AM
|
|
|
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';
Request:
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
|
|
|
Posts:
437
Registered:
12/7/06
|
|
|
|
Re: Altering a table that is referenced by FK results in error.
Posted:
Apr 16, 2012 4:09 AM
in response to: chris.rokusek_218
|
|
|
Hi,
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).
|
Regards, Valentine
/R&D @ DELL
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
