CODEWITHSUNDEEP
sql-serverforeign-keys
Angelina
Angelina

Reputation: 2265

Is it safe to delete same foreign keys

i was looking at the SSMS generated script of all tables and I noticed this:

/****** Object:  ForeignKey [FK_User__ACRNL__1C722D53]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])
GO
/****** Object:  ForeignKey [FK_User__ACRNL__1D66518C]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])
GO
/****** Object:  ForeignKey [FK_User__ACRNL__1E5A75C5]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])
GO
/****** Object:  ForeignKey [FK_User__ACRNL__4EFDAD20]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])
GO
/****** Object:  ForeignKey [FK_User__ACRNL__4FF1D159]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])
GO
/****** Object:  ForeignKey [FK_User__ACRNL__50E5F592]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])
GO
/****** Object:  ForeignKey [FK_User_ACRNL_7834CCDD]    Script Date: 10/19/2015 16:28:12 ******/
ALTER TABLE [dbo].[User_Funding]  WITH CHECK ADD FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])

From what I see there are 7 foreign keys on Location within User_Funding table.

Can I delete 6 of these foreign keys, or I should dig deeper to finding out why there are 7 foreign keys?

Upvotes: 0

Views: 91

Answers (1)

sean
sean

Reputation: 1205

Yes. Delete all but one.

You probably have some SQL that is being run to publish updates. It may be checking to see if the FK already exists by the FK name but then creates a FK without a name which yields the random "_7834CCDD".

Change it to look like:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_Funding_Location]') AND parent_object_id = OBJECT_ID(N'[dbo].[User_Funding]'))
ALTER TABLE [dbo].[User_Funding]  ADD  CONSTRAINT [FK_User_Funding_Location] FOREIGN KEY([ACRNLocation])
REFERENCES [dbo].[Location] ([location])

The [FK_User_Funding_Location] after "ADD CONSTRAINT" ensures the name is not random.

I have dealt with this issue and even had to write a script to delete all but one and rename the last one to something non-random.

Upvotes: 2

Related Questions