Database design - How can I have a foreign key of the primary key in the same table?

Question

My database has to store all the available departments in my company.

Some departments are sub-departments on another existing department. I've decided to solve this like this:

Departments
ID Description HeadOfDepartment ParentDepartment

ParentDepartment can be null, indicating it is a root department. If it has a parent I'll act accordingly, my question is how can I code this in Microsoft SQL?

Answer 1

Create a foreign key on ParentDepartment that refrences the ID property of the table.

CREATE TABLE dbo.Departments
    (
    ID int NOT NULL IDENTITY (1, 1),
    Description nvarchar(100) NOT NULL,
    HeadOfDepartment nvarchar(100) NOT NULL,
    ParentDepartment int NULL
    )  ON [PRIMARY]

ALTER TABLE dbo.Departments ADD CONSTRAINT
    PK_Departments PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE dbo.Departments ADD CONSTRAINT
    FK_Departments_Departments FOREIGN KEY
    (
    ParentDepartment
    ) REFERENCES dbo.Departments
    (
    ID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

Answer 2

If you create a foreign key, and enforce it, then you'll not be allowed to put null values in the foreign key field. If I were to implement something like this, I would enforce the foreign key constraint, and simply fill the foreign key value of a department with no parent with it's own primary key. That should be allowed.

CREATE TABLE Departments 
(
    Id INT PRIMARY KEY, 
    Description VARCHAR(255), 
    HeadOfDepartment VARCHAR(255), 
    ParentDepartment INT NOT NULL REFERENCES Departments(Id)
);

Answer 3

CREATE TABLE Departments 
(
    ID integer primary key, 
    Description varchar(255), 
    HeadOfDepartment varchar(255), 
    ParentDepartment integer references Departments(ID)
);

Answer 4

Foreign keys in SQL Server are allowed to be either NULL OR a valid key in the appropriate table.

CREATE TABLE [hierarchytest](
    [ID] [int] NOT NULL,
    [ParentID] [int] NULL,
 CONSTRAINT [PK_hierarchytest] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))
GO
ALTER TABLE [hierarchytest]  WITH CHECK ADD  CONSTRAINT [FK_hierarchytest_hierarchytest] FOREIGN KEY([ParentID])
REFERENCES [hierarchytest] ([ID])
GO
ALTER TABLE [hierarchytest] CHECK CONSTRAINT [FK_hierarchytest_hierarchytest]