Database Engine error while handling EmployeeID

devid brevis 0 Reputation points
2025-09-23T14:28:02.0633333+00:00

-- Step 1: Create Test Database

CREATE DATABASE EmployeeDB_AzureTest;

GO

-- Step 2: Use Database

USE EmployeeDB_AzureTest;

GO

-- Step 3: Create Employees Table

CREATE TABLE Employees (

EmployeeID BIGINT NOT NULL PRIMARY KEY,

FullName NVARCHAR(100),

Department NVARCHAR(50),

CreatedDate DATETIME DEFAULT GETDATE()
```);

GO

-- Step 4: Insert Valid Row

INSERT INTO Employees (EmployeeID, FullName, Department)

VALUES (1866xxxxxxx, 'John Doe', 'Finance');

GO

-- Step 5: Insert Duplicate EmployeeID (Error: Violation of PRIMARY KEY)

INSERT INTO Employees (EmployeeID, FullName, Department)

VALUES (1866xxxxxxx, 'Jane Smith', 'HR');

GO

-- Step 6: Wrong column name (Error: Invalid column 'Dept')

INSERT INTO Employees (EmployeeID, FullName, Dept)

VALUES (1866xxxxxx, 'Michael Scott', 'Sales');

GO

-- Step 7: Insert NULL into EmployeeID (Error: Cannot insert NULL)

INSERT INTO Employees (EmployeeID, FullName, Department)

VALUES (NULL, 'Sarah Lee', 'IT');

GO

-- Step 8: Search EmployeeID

SELECT * FROM Employees

WHERE EmployeeID = 1866xxxxxxx;

GO

-- Step 9: Update using invalid column (Error: Invalid column 'EmpDept')

UPDATE Employees

SET EmpDept = 'Operations'

WHERE EmployeeID = 1866xxxxxxx;

GO

-- Step 10: Join with non-existing table (Error: Invalid object name 'Departments')

SELECT e.EmployeeID, e.FullName, d.DeptName

FROM Employees e

JOIN Departments d ON e.EmployeeID = d.EmpID

WHERE e.EmployeeID = 1866xxxxxxx;

GO

Msg 2627, Level 14, State 1, Line 15

Violation of PRIMARY KEY constraint 'PK__Employee__7AD04F11'. Cannot insert duplicate key in object 'dbo.Employees'.

The duplicate key value is (1866xxxxxxx).

Msg 207, Level 16, State 1, Line 21

Invalid column name 'Dept'.

Msg 515, Level 16, State 2, Line 27

Cannot insert the value NULL into column 'EmployeeID', table 'EmployeeDB_AzureTest.dbo.Employees'; column does not allow nulls. INSERT fails.

Msg 207, Level 16, State 1, Line 35

Invalid column name 'EmpDept'.

Msg 208, Level 16, State 1, Line 42

Invalid object name 'Departments'.

SQL Server Database Engine

Answer recommended by moderator
Anonymous
2025-10-17T08:21:24.96+00:00

Hi devid brevis ,

Thanks for reaching out to the Microsoft Q & A Forum.

To fix your SQL script, you’ll need to correct both syntax and logical errors. Clean up the table creation syntax by removing extra words like stray backticks, ensuring the CREATE TABLE statement ends properly with a single closing parenthesis. Avoid inserting duplicate primary keys since each EmployeeID must be unique. Ensure that all column names are valid by changing Dept to Department in the INSERT statement and EmpDept to Department in the UPDATE statement. Respect NOT NULL constraints by providing a valid value for EmployeeID instead of NULL. If you are joining tables, make sure referenced objects like Departments exist; otherwise, create them or remove the join. Once these syntax and logic issues are corrected, your script should execute successfully without errors.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

Was this answer helpful?

0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.