Foreign keys are an important part of making sure the entities in our relational database can be connected back together and queried correctly. It's part of proper data normalization. This isn't shocking, of course: the order line table has a foreign key over to the products table, so that every line item points to a product we sell. That's database 101. Database 201, you'd also think about the times where you have a hierarchy of records, where say, every Employee has a ManagerId, which is a foreign key back to the Employee table itself (and in database 301, you'll understand why having a ManagerId field is a terrible choice, but that's neither here nor there).

The important thing here is that foreign keys are very common, and foreign keys pointing back to the same table are also very common.

But it can still go very wrong. Roman sends us this foreign key definition in a database they maintain.

ALTER TABLE [dbo].[ListingDetails]  WITH CHECK ADD  CONSTRAINT [FK_Listings_Listings] FOREIGN KEY([Id])
REFERENCES [dbo].[ListingDetails] ([Id])

This ensures that the ListingDetails table's Id field is a foreign key over to the ListingDetails table's Id field. A field is a foreign key back to itself.

Roman writes: "I can't believe SQL Server even allows this..."

I can. While I haven't personally tried this out, a bit of cursory searching implies that this does actually work- the record satisfies its own constraint, so you can still add it to the database. It's basically a no-op, which isn't great, but it's not SQL Server's job to ensure you keep all your toes because they've removed all the footguns.

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!