Have Geek Brain

Database design. How can I get the foreign key of a primary key in one table?

My database needs to store all available departments in my company.

Some departments are subdivisions of another existing department. I decided to solve it this way:

Departments
ID Description HeadOfDepartment ParentDepartment

The ParentDepartment can be null to indicate that this is the root department. If I have a parent I will act accordingly, my question is how can I encode it in Microsoft SQL?

+2


a source to share


4 answers 


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


+4


a source


Foreign keys in SQL Server can be either NULL or a valid key in the corresponding 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]
+1


a source


If you create a foreign key and apply it, then you will not be allowed to enter null values ​​in the foreign key field. If I implemented something like this, I would use a foreign key constraint and just populate the foreign key value for the unparent department with its own primary key. This should be allowed.

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


a source


Create a foreign key to ParentDepartment

that updates the table id property.

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
-1


a source






More articles:


All Articles