SQL Server Database Engine error during insert/update sequence in employee audit table

I was running a SQL Server test for an internal employee-audit scenario.

The script creates two tables, inserts a few rows and performs joins/updates.

Expected:

• All inserts and updates should complete successfully.

Observed:

• Several statements fail with duplicate key errors, invalid column names and foreign key constraint issues.

Can someone explain which parts of this script cause these errors and why?

-- Create test database

CREATE DATABASE AuditDB_Test;

GO

USE AuditDB_Test;

GO

-- Step 1: Create main Employee table

CREATE TABLE EmployeeInfo (

EmpID BIGINT PRIMARY KEY,

EmpName NVARCHAR(120),

Dept NVARCHAR(50),

Salary DECIMAL(12,2),

CreatedAt DATETIME DEFAULT SYSDATETIME()

);

GO

-- Step 2: Create Audit table with foreign key

CREATE TABLE AuditLog (

LogID INT IDENTITY(1,1) PRIMARY KEY,

EmpRef BIGINT NOT NULL,

ChangeType NVARCHAR(50),

ChangeDate DATETIME DEFAULT SYSDATETIME(),

FOREIGN KEY (EmpRef) REFERENCES EmployeeInfo(EmpID)

);

GO

-- Step 3: Insert a valid employee

INSERT INTO EmployeeInfo (EmpID, EmpName, Dept, Salary)

VALUES (18884006486, 'Sarah T', 'Finance', 75000.00);

GO

-- Step 4: Duplicate insert (Error)

INSERT INTO EmployeeInfo (EmpID, EmpName, Dept, Salary)

VALUES (18884006486, 'Sarah T (Duplicate)', 'Finance', 76000.00);

GO

-- Step 5: Insert another valid record

INSERT INTO EmployeeInfo (EmpID, EmpName, Dept, Salary)

VALUES (18884006486, 'Michael K', 'HR', 68000.00);

GO

-- Step 6: Audit insert referencing valid employee

INSERT INTO AuditLog (EmpRef, ChangeType)

VALUES (18884006486, 'CREATE');

GO

-- Step 7: Audit insert referencing non-existent EmpRef (Error: FK constraint)

INSERT INTO AuditLog (EmpRef, ChangeType)

VALUES (18884006486, 'UPDATE');

GO

-- Step 8: Typo in column name (Error)

UPDATE EmployeeInfo

SET Salery = 80000

WHERE EmpID = 18884006486;

GO

-- Step 9: Missing FROM keyword (Syntax error)

SELECT EmpID EmpName, Dept, Salary

EmployeeInfo;

GO

-- Step 10: Join with non-existing table (Error)

SELECT e.EmpID, e.EmpName, d.DeptTitle

FROM EmployeeInfo e

JOIN DepartmentList d ON e.Dept = d.DeptTitle;

GO

-- Step 11: Aggregation check (works fine)

SELECT Dept, COUNT(*) AS TotalEmp, AVG(Salary) AS AvgSalary

FROM EmployeeInfo

GROUP BY Dept;

GO

⚙️ Sample Error Output

Msg 2627, Level 14, State 1

Violation of PRIMARY KEY constraint 'PK__EmployeeInfo__...'. Cannot insert duplicate key.

The duplicate key value is (18883000000).

Msg 547, Level 16, State 0

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__AuditLog__EmpRef__...".

The conflict occurred in database "AuditDB_Test", table "dbo.EmployeeInfo".

Msg 207, Level 16, State 1

Invalid column name 'Salery'.

Msg 156, Level 15, State 1

Incorrect syntax near 'EmployeeInfo'.

Msg 208, Level 16, State 1

Invalid object name 'DepartmentList'.