Join a table to itself

this is one on my database tables template.

Id int开发者_运维百科 PK
Title nvarchar(10) unique
ParentId int 

This is my question.Is there a problem if i create a relation between "Id" and "ParentId" columns? (I mean create a relation between a table to itself) I need some advices about problems that may occur during insert or updater or delete operations at developing step.thanks

---

You can perfectly join the table with it self.

You should be aware, however, that your design allows you to have multiple levels of hierarchy. Since you are using SQL Server (assuming 2005 or higher), you can have a recursive CTE get your tree structure.

Proof of concept preparation:

declare @YourTable table (id int, parentid int, title varchar(20))

insert into @YourTable values
(1,null, 'root'),
(2,1,    'something'),
(3,1,    'in the way'),
(4,1,    'she moves'),
(5,3,    ''),
(6,null, 'I don''t know'),
(7,6,    'Stick around');

Query 1 - Node Levels:

with cte as (
    select Id, ParentId, Title, 1 level 
    from @YourTable where ParentId is null

    union all

    select yt.Id, yt.ParentId, yt.Title, cte.level + 1
    from @YourTable yt inner join cte on cte.Id = yt.ParentId
)
select cte.*
from cte 
order by level, id, Title

---

No, you can do self join in your table, there will not be any problem. Are you talking which types of problems in insert, update, delete operation ? You can check some conditions like ParentId exists before adding new record, or you can check it any child exist while deleting parent.

You can do self join like :

select t1.Title, t2.Title as 'ParentName'
from table t1 
left join table t2 
on t1.ParentId = t2.Id  

---

You've got plenty of good answers here. One other thing to consider is referential integrity. You can have a foreign key on a table that points to another column in the same table. Observe:

CREATE TABLE tempdb.dbo.t
(
    Id INT NOT NULL ,
    CONSTRAINT PK_t PRIMARY KEY CLUSTERED ( Id ) ,
    ParentId INT NULL ,
    CONSTRAINT FK_ParentId FOREIGN KEY ( ParentId ) REFERENCES tempdb.dbo.t ( Id )
)

By doing this, you ensure that you're not going to get garbage in the ParentId column.

---

Its called Self Join and it can be added to a table as in following example

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id 

---

I have seen this done without errors before on a table for menu hierarchy you shouldnt have any issues providing your insert / update / delete queries are well written.

For instance when you insert check a parent id exists, when you delete check you delete all children too if this action is appropriate or do not allow deletion of items that have children.

---

It is fine to do this (it's a not uncommon pattern). You must ensure that you are adding a child record to a parent record that actually exists etc., but there's noting different here from any other constraint.

You may want to look at recursive common table expressions:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

As a way of querying an entire 'tree' of records.

---

This is not a problem, as this is a relationship that's common in real life. If you do not have a parent (which happens at the top level), you need to keep this field "null", only then do update and delete propagation work properly.