Share via

SQL Server Error: Cannot insert duplicate key for EmployeeID

Gaurav Kumar 5 Reputation points
2025-10-01T09:02:44.05+00:00

-- Step 1: Create Test Database

CREATE DATABASE EmployeeDBTest2;

GO

-- Step 2: Switch to Database

USE EmployeeDBTest2;

GO

-- Step 3: Create Employee Table

CREATE TABLE Employees (

EmployeeID BIGINT NOT NULL PRIMARY KEY,

Name NVARCHAR(100),

varchar 18663477488

Department NVARCHAR(50),

CreatedDate DATETIME DEFAULT GETDATE()


GO

-- Step 4: Insert Records (Valid)

INSERT INTO Employees (EmployeeID, Name, Department)

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

GO

-- Step 5: Insert Duplicate EmployeeID (Will cause PK violation)

INSERT INTO Employees (EmployeeID, Name, Department)

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

GO

-- Step 6: Insert EmployeeID with + sign (Error: conversion failed)

INSERT INTO Employees (EmployeeID, Name, Department)

VALUES (+1866xxxxxxx, 'Michael Scott', 'Sales');

GO

-- Step 7: Insert as string (works but implicit conversion warning)

INSERT INTO Employees (EmployeeID, Name, Department)

VALUES ('1866xxxxxxx', 'Dwight Schrute', 'Sales');

GO

-- Step 8: Select all rows

SELECT * FROM Employees;

GO

-- Step 9: Search specific EmployeeID

SELECT * FROM Employees

WHERE EmployeeID = 1866xxxxxxx;

GO

-- Step 10: Wrong column name (Error: Invalid column EmpID)

SELECT EmpID, Name

FROM Employees

WHERE EmployeeID = 1866xxxxxxx;

GO

-- Step 11: Insert NULL EmployeeID (Error: Cannot insert NULL)

INSERT INTO Employees (EmployeeID, Name, Department)

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

GO

-- Step 12: Update Employee (Valid)

UPDATE Employees

SET Department = 'Operations'

WHERE EmployeeID = 1866xxxxxxx;

GO

-- Step 13: Update with invalid column (Error)

UPDATE Employees

SET Dept = 'Marketing'

WHERE EmployeeID = 1866xxxxxxx;

GO

-- Step 14: Delete employee

DELETE FROM Employees

WHERE EmployeeID = 1866xxxxxxxx;

GO

-- Step 15: Try join with non-existing table (Error)

SELECT e.EmployeeID, e.Name, d.DeptName

FROM Employees e

JOIN Departments d ON e.EmployeeID = d.EmpID

WHERE e.EmployeeID = 1866xxxxxxx;

GO

-- Step 16: Final check

SELECT * FROM Employees;

GO
SQL Server | SQL Server Transact-SQL

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Gaurav Kumar 5 Reputation points
    2025-10-01T09:05:36.22+00:00

    In Step #5 error will arise because the EmployeeID is a Primary Key. Meaning that data entered there shall be unique. Primary Key also is the basis of your created table as a sorting key and index key too.

    In Step #6 the error will arise because you add a plus sign (+) which no need to put because all the numbers entered without a sign is considered positive number. On the other hand, only put negative sine (-) if it is a negative numbers.

    In Step #7 a warning arise because the numbers are inside a single quote (' ') which is not to be because you are dealing with a numbers on that column not an alphanumeric type.

    In Step #10 an error rise again because the EmpID is not defined or present in the table created.

    In Step #11, the column EmployeeID is a primary key which will not accept a null. An Index Unique will.

    Step #12 all data are valid. Because the columns name are spelled correctly and the data are also correct according its data types.

    Step #13 is error because the column Dept is not define or present in table Employees.

    Step #14 will delete only to specific employee which has that unique ID number only.

    Step #15. Yes, it will raise an error because the Departments table is not exist in current database.

    Step #16 will show all existing data or records in that Employees table with all the corresponding columns too.

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.