[SQL] 自我參考 Foreign Key

以往使用 Foreign Key 都是在不同 Table 之間設定，看 EF 影片教學時才意識到，Foregin Key 也可以設在自身

CREATE TABLE [dbo].[MyEmployees]
(
  [EmployeeID] [smallint] NOT NULL,
  [FirstName] [nvarchar](30) NOT NULL,
  [LastName] [nvarchar](40) NOT NULL,
  [Title] [nvarchar](50) NOT NULL,
  [DeptID] [smallint] NOT NULL,
  [ManagerID] [smallint] NULL,
  CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED 
  (
    [EmployeeID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyEmployees]  WITH CHECK ADD  CONSTRAINT [FK_MyEmployees_MyEmployees1] FOREIGN KEY([ManagerID])
REFERENCES [dbo].[MyEmployees] ([EmployeeID])
GO

ALTER TABLE [dbo].[MyEmployees] CHECK CONSTRAINT [FK_MyEmployees_MyEmployees1]
GO

FK GUI 設定畫面

SSMS 圖表