When you insert a record into a table, the domain integrity applies. It does not have the information of the existing table data.
When you insert a record to a table, the data of whole column(s) are considered.
When you insert a record in the referencing table, the referenced table will be checked against.
When you insert a record the table, the field ManagerID will reference the whole column of EmployeeID.
Example of self-referencing:
CREATE TABLE [dbo].[Emp](
[EmployeeID] [int] NOT NULL,
[ManagerID] [int] NULL,
[Title] [varchar](50) NULL,
CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED
([EmployeeID])
)
ALTER TABLE dbo.Emp
ADD CONSTRAINT FK_EmployeeID_ManagerID Foreign KEY(ManagerID)
REFERENCES Emp(EmployeeID)
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(1,NULL,NULL);
UPDATE dbo.Emp
SET ManagerID=1,Title='Sales Manager'
WHERE EmployeeID=1;
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(2,1,'Sales Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(3,1,'Sales Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(4,4,'Production Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(5,4,'Production Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(6,4,'Production Manager');
Attention:
ManagerID allows NULL.
If ManagerID column does not allow NULL, you must insert a record before you apply the foreign key.
