Recursive (Self Reflexive) Foreign Key

[sidel]

I'm a relative newbie to SQL. <br><br>I'm trying to conceptualize an example of a table that would have a recursive foreign key. I understand this to be a foreign key in a table that references the primary key of the same table. <br><br>Thanks for any help.

 

[foxdev]

Imagine an Employee table.&nbsp;&nbsp;All employees except the boss have a supervisor; many of the employees are also supervisors themselves.&nbsp;&nbsp;Officially, each employee has only one supervisor (though we all know that reality doesn't quite work that way).<br><br>So imagine this table:<br><br><FONT FACE=monospace>EmployeeID PK<br>LastName<br>FirstName<br>SupervisorID</font><br><br>In this case, SupervisorID is a foreign key into the same table (it references EmployeeID), because every supervisor is also an employee. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[KrisSimonis]

On a same note as this, I have a situation
like this in the database I'm transporting to
a microsoft SQL server, however when I try to
add the foreign key to this table I get an error
message.
unid = unique ID field in the table
parentid = field in same table that references unid field.

Statement:
ALTER TABLE CLASSIFICATIE
ADD CONSTRAINT FK_CLASSIFICATIE_parentid FOREIGN KEY (parentid) REFERENCES CLASSIFICATIE(unid)

Error Message:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'FK_CLASSIFICATIE_parentid'. The conflict occurred in database 'Database', table 'CLASSIFICATIE', column 'unid'.