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

During a database migration, I've run across a database table constraint of the form:

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

Why would one do this? This was originally done on a Sybase database, and we are converting to SQL Server 2008 R2.

UPDATE : Yes, the foreign key constraint is a field referencing the same table AND SAME FIELD.

I ran this query on the source Sybase database and found 42 of these crazy keys defined, so it doesn't seem 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

---

I believe that hierarchies are the standard examples you'll find in books whenever you use foreign keys for the same table, such as:

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

What you posted looks like a wrong application of this hierarchy relation in the same table. I'm not entirely sure why you'd ever wanna do that.

Hope this helped =)

---

Surprise! This totally 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 have been a mistake (Typo? Dragging a column onto itself in a diagram?) or used to fool another system (ORM?) into some particular behavior. I will be very curious to see if someone comes up with a legit reason.

UPDATE: As cleverly suggested by @8kb, this could have been an attempt to prevent truncation, but we can see from my example that even truncation works just fine.

---

I guess it's a bug in database model.

It's really weird. I can't imagine what usefull purpose is this construction.

The only way how to insert data is without checking reference integrity. It means with explicitly disabled references or with some kind of bulk insert and so on.

链接地址: http://www.djcxy.com/p/11292.html

上一篇: 请求参数外部控制器

下一篇: 引用自身的外键列的作用（或目的）是什么？