a table that has a foreign key that references itself

Question

Post reply

a table that has a foreign key that references itself

S-322532

SSCommitted

Points: 1613
More actions
August 25, 2008 at 12:22 am

#94504

Hi,
Recently i read the following statement
You cannot use TRUNCATE TABLE on tables that:
Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
Can any one give me an example of "a table that has a foreign key that references itself" and scenarios where it is used. :hehe:
Thanks
S

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Hierachies is the classic example, when you have a parent-child relationship within a single table.

create table Employees (

EmployeeID int identity primary key,

EmployeeName varchar(50),

ManagerID int

)

ALTER TABLE Employees ADD Constraint fk_Manager_Employee Foreign key (managerID) references Employees(EmployeeID)

I'm not going to say it's a good idea or a bad idea. It can be hard to work with sometimes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arjun.tewari SSC Eights! Points: 983 More actions · Answer 2

One example that I can think of can be the EmployeeDetails table. This table stores the EmployeeID as well as the ManagerID of the employee. In such a situation, managerid will reference to Employeeid.

As per the database design percepective, I would like to implement such a situation using a Trigger instead of a foriegn key percpective.(Inviting others to intervene).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

arjun.tewari (8/25/2008)
As per the database design percepective, I would like to implement such a situation using a Trigger instead of a foriegn key percpective.(Inviting others to intervene).

I'd much prefer the foreign key. Triggers are expensive, they occur after the insert/update/delete has occured and if rolled back require the insert/update/delete to be undone. Foreign keys are checked before the insert/update/delete occurs and hence won't incur the cost of the operation and the rollback if the check fails.

Also, the optimiser can sometimes use foreign keys to get more information about the data in the table and construct a better execution plan for a query than it otherwise could.

Personally, the only thing I like using triggers for is auditing, and that's probably going to change in SQL 2008.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

john.arnott SSChampion Points: 11882 More actions · Answer 4

What are the comparitive advantages of having rows refer to other rows in the same table rather than setting up an associative table? My instinct (as an application programmer -- I'm not a DBA) would be to establish a separate table for flexibility (when in doubt, normalize). Then, an employee in the example above could be related to a manager AND to a union shop steward (or bowling team captain....), for example, without changing the table structures.

With that in mind, how much performance advantage does having "manager_ID" in the employee table give? Is it worth it?