Do I need to create an Index manually when setting up a foreign key?

Question

I have these schema in my SQL Server database:

CREATE TABLE [dbo].[Phrase] ( [PhraseId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [Text] NVARCHAR (MAX) NOT NULL, [CategoryId] INT DEFAULT ((1)) NOT NULL, PRIMARY KEY CLUSTERED ([PhraseId] ASC), CONSTRAINT [FK_PhrasePhraseCategory] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[PhraseCategory] ([PhraseCategoryShortId]) ); CREATE TABLE [dbo].[PhraseCategory] ( [PhraseCategoryId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [PhraseCategoryShortId] INT IDENTITY (1, 1) NOT NULL, [Name] VARCHAR (20) NOT NULL, PRIMARY KEY CLUSTERED ([PhraseCategoryShortId] ASC) );

Am I correct in saying that if I go to the PhraseCategory table and try to delete a row it will check to see if that PhraseCategoryShortId is used in the phrase table? If that's a case then should I index CategoryId in that table?

Mureinik · Accepted Answer · 2016-09-07 06:05:04Z

Yes and yes. This foreign key establishes a constraints such that a Phrase must have a valid PhraseCategory. When a category is deleted, the PhraseCategory is deleted, the Phrase table is checked to validate that no "orphan" Phrases are left.

While a foreign key must always refer to a unique value (either via an explicit constraint or a primary key), the referring field does not have to be index, so you'll need to index it yourself if you care about the performance of a delete operation on PhraseCategory.

Collectives™ on Stack Overflow

Do I need to create an Index manually when setting up a foreign key?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related