I just want to clear doubts about 1 to 1 relationship in sql. To understand i have created following example.
Please let me know is this the correct way to implement(1:1) relationship or not.
What I have tried:
CREATE TABLE tbEmployee (
EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
EmployeeName VARCHAR(100)
);
GO
CREATE TABLE tbEmployeeIdentityDetails (
RecordId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
AadharNumber VARCHAR(20) NOT NULL,
EmployeeId INT UNIQUE FOREIGN KEY REFERENCES tbEmployee(EmployeeId)
);
GO
INSERT INTO tbEmployee (EmployeeName)
VALUES
('Aman'),
('Kapil'),
('Vijay'),
('Panjak');
GO
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987', 1),
('6432 3246 9097', 2),
('8875 8746 9234', 3),
('4678 4526 6654', 4);
GO
SELECT * FROM tbEmployee
SELECT * FROM tbEmployeeIdentityDetails
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987',4);
Exceptions: Violation of UNIQUE KEY constraint 'UQ__tbEmploy__7AD04F10917285FA'. Cannot insert duplicate key in object 'dbo.tbEmployeeIdentityDetails'. The duplicate key value is (4).
The statement has been terminated.
It is allowing only one record for each employees in tbEmployeeIdentityDetails table which is correct. But i think it should follow the concept: One aadhar number can belong to only one 1 employee and 1 employee can have only one aadhar number since aadhar number is the unique id for each employee in India