First things first. As OG (Original Griff) stated the AadharNumber
would need to have a Unique Index applied to it. If it going to be kept in the Employee Identity Details
table then you can run this code:
CREATE UNIQUE NONCLUSTERED INDEX [IX_EmployeeIdentity_AadharNumber]
ON dbo.tbEmployeeIdentityDetails ( AadharNumber)
Second thing, as OG questioned; why are these in separate tables? The only real reason to split the information up is if the row size is pushing 8K OR if amount of data is causing performance issues. The negatives of this design are the overhead of having an additional table, the performance impacts of the Foreign Key constraint (which essentialy has to do a select on the first table), and then there are the queries needed to get all the data. Another thing about the FK Constraint- unless you are doing complex star joined queries, they are of little to no benefit when it comes to performance.
As for the Employee table... if you are not going to add the Aadhar to it, it will need more unique information. Some names are quite common and a collision is likely.
Now, for just testing and learning, we'll leave things as they are and assume that when you actually build something real you will follow the above suggestions.
And my suggestions.
I generally recommend against "direct" table access (eg Insert, Select, Edit, Delete) and prefer to use Stored Procedures. With the two INSERT queries you had I noted that you aren't always guaranteed that the second set IDs posted will match the first. If this was done via a program this would eliminate round trips between the app and database with this, and you could even return the New Employee ID like this:
CREATE PROCEDURE dbo.EmployeeAndDetails_Create (
SET NOCOUNT ON
DECLARE @EmpID INT = -1
INSERT tbEmployee (EmployeeName)
SET @EmpID = Scope_Identity()
INSERT tbEmployeeIdentityDetails (EmployeeId, AadharNumber)
VALUES (@EmpID, @Aadhar)
SELECT NewEmployeeID = @EmpID