What is the effect (or purpose) of a foreign key column referencing it?

Question

During the database migration, I encountered a limitation of the form database table:

ALTER TABLE [dbo].[myTable] ADD CONSTRAINT [someName] FOREIGN KEY ([id]) REFERENCES [dbo].[myTable] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION 

Why do this? This was originally done in the Sybase database, and we are converting to SQL Server 2008 R2.

UPDATE : Yes, a foreign key constraint is a field that references the same table AND SUCH A FIELD.

I ran this query in the original Sybase database and found 42 of these crazy keys, so it doesn't look like a typo.

 SELECT sr.constrid as [Constraint ID], so.name as [Table], sc.name as [Column] FROM sysreferences sr INNER JOIN sysobjects so ON (so.id = sr.tableid) INNER JOIN syscolumns sc ON (sc.id = sr.tableid AND sc.colid = sr.fokey1) WHERE sr.tableid = sr.reftabid AND sr.fokey1 = sr.refkey1 AND sr.fokey2 = 0 AND sr.refkey2 = 0 

Answer 1

I believe that hierarchies are standard examples that you will find in books when you use foreign keys for the same table, for example:

 create table Employees ( EmployeeID int identity primary key, EmployeeName varchar(50), ManagerID int Foreign key (managerID) references Employees(EmployeeID) ) 

What you posted seems to be misapplying this hierarchy relationship in the same table. I'm not quite sure why you ever wanted to do this.

Hope this helped =)

Answer 2

Surprise! This fully works:

 create table crazy ( ID int primary key references crazy (ID) -- This runs ); insert into crazy select 1; -- So does this select * from crazy; -- Returns 1 truncate table crazy; -- Works just fine 

I can only think that this would be a mistake (Typo? Dragging and dropping a column onto a diagram?) Or is used to trick another system (ORM?) Into some specific behavior. I will be very curious to find out if anyone has a reasonable reason.

UPDATE: as @ 8kb suggested, this might be an attempt to prevent truncation, but from my example it can be seen that even truncation works very well.

Answer 3

I assume this is a bug in the database model.

This is really weird. I cannot imagine what a useful purpose this construction is.

The only way to insert data is without checking the integrity of the link. This means that with links explicitly disabled or with some kind of bulk insert, etc.

Answer 4

The effect of a foreign key column referencing itself seems to be nothing. The question of why the database engine will allow you to do such a useless thing remains unresolved.

However, I believe that the reason someone would build a foreign key, such as laziness / negligence. I found out that in SQL Server Management Studio, if you create a foreign key using the GUI (instead of writing it to T-SQL), the initial behavior of SSMS is to create a foreign key in exactly the same way as in this question :

• Expand any database table in the Object Explorer pane. We will call this table TableA.
• Rt-click on Keys under the TableA node and select New Foreign Key ... This will open the "Edit Table" panel and the "Foreign Keys" dialog.
• After changing anything, just click the Close button in the Foreign Relationships dialog box. "Unfortunately, I did not want to try to add a foreign key."
• Closing the dialog still created a foreign key named FK_TableA_TableA and selected the primary key column as the base and reference column.
• When the Modify panel is still open (that it still closes the Foreign Key Relationships dialog box), close it. It has changes (the new foreign key you just created). Save these changes.
• A new new key now exists in the database for TableA, with the primary key column referencing it.