Click here to Skip to main content
14,154,232 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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


--On trying to insert another record for the same employee generates error e.g.

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
Posted
Updated 19-Mar-19 10:00am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

If every employee has one and only one Aadhar number, why are you storing it in a separate table at all? Add it as a column to the Employee table, and make that UNIQUE and NOT NULL as well. No point in a separate table and a relationship if there is only one value for each employee!
   
Comments
Maciej Los 19-Mar-19 6:35am
   
5ed!
Webcodeexpert.com 19-Mar-19 7:13am
   
Actually i know the point as you mentioned. But i just want to understand the 1-to-1 relationship so i created this example. And there may be multiple columns in tbEmployeeIdentityDetails table along with just AadharNumber. In that case what should be the design of the table? If you can let me know any other example through which i can understand the concept and practical use of 1-to-1 then it will be great..thanks in advance
OriginalGriff 19-Mar-19 7:32am
   
You can't enforce a one-to-one relationship using a third table, because you can always enter a value in Employee or EmployeeDetails without a corresponding row in the "relationship" table at all. And you can't establish a true one-to-one relationship between two tables because you can't physically insert a row in both tables simultaneously, and you need to - if you don't, you get a Constraint error of whichever table you insert first. That's why a single table solution is pretty much the only true one-to-one relationship, because the relation is data dependant.
Webcodeexpert.com 20-Mar-19 2:34am
   
Actually i was following these 3 articles
https://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

https://howtoprogramwithjava.com/database-relationships-many-many-one-one/

but could not get the proper way and use of 1-TO-1 Relationship. Please suggest which one should i follow..
Webcodeexpert.com 25-Mar-19 3:21am
   
thanks for your comments. I have redesigned the table and its is working as required. Please have a look and let me know is this correct or not

go
CREATE TABLE tbEmployee (
EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
EmployeeName VARCHAR(100)
);


GO
CREATE TABLE tbEmployeeIdentityDetails (
EmployeeId INT PRIMARY KEY FOREIGN KEY REFERENCES tbEmployee(EmployeeId),
AadharNumber VARCHAR(20) NOT NULL,
CONSTRAINT UC_Employee_AadharNumber UNIQUE (AadharNumber)
);


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)


GO
SELECT * FROM tbEmployee
SELECT * FROM tbEmployeeIdentityDetails



if we try to insert identity details for same EmployeeId which already exists in the tbEmployeeIdentityDetails table it will show "Violation of PRIMARY KEY constraint" error

INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('5543 4532 1123',2);


if we try to insert identity details for the an EmployeeId which does not exists in the tbEmployee table it givens Foreign key violation error
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('8157 8789 4987',9);


if we try to insert existing aadhar number for an EmployeeId it will show "Violation of UNIQUE KEY constraint" erro
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987',4);
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I completely agree with OG and i wanted to point you out to this article: One-to-one (data model) - Wikipedia[^], which explains a one-to-one relationship in details:
Quote:
It is important to note that a one-to-one relationship is not a property of the data, but rather of the relationship itself. A list of mothers and their children may happen to describe mothers with only one child, in which case one row of the mothers table will refer to only one row of the children table and vice versa, but the relationship itself is not one-to-one, ...
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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)
GO


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 (
	@Name NVARCHAR(32),
	@Aadhar NVARCHAR(14)
 ) AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @EmpID INT = -1

	INSERT tbEmployee (EmployeeName)
	VALUES (@Name)

	SET @EmpID = Scope_Identity()

	INSERT tbEmployeeIdentityDetails (EmployeeId, AadharNumber)
	VALUES (@EmpID, @Aadhar)

	SELECT NewEmployeeID = @EmpID
END
GO
   
Comments
Webcodeexpert.com 20-Mar-19 2:34am
   
thanks for you reply..Actually i was following these 3 articles
https://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

https://howtoprogramwithjava.com/database-relationships-many-many-one-one/

but could not get the proper way and use of 1-TO-1 Relationship. Please suggest which one should i follow..
MadMyche 20-Mar-19 7:03am
   
How to program with Javas seems to be the best so far; but I didn't go through the full series
Webcodeexpert.com 25-Mar-19 3:21am
   
thanks for your comments. I have redesigned the table and its is working as required. Please have a look and let me know is this correct or not

go
CREATE TABLE tbEmployee (
EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
EmployeeName VARCHAR(100)
);


GO
CREATE TABLE tbEmployeeIdentityDetails (
EmployeeId INT PRIMARY KEY FOREIGN KEY REFERENCES tbEmployee(EmployeeId),
AadharNumber VARCHAR(20) NOT NULL,
CONSTRAINT UC_Employee_AadharNumber UNIQUE (AadharNumber)
);


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)


GO
SELECT * FROM tbEmployee
SELECT * FROM tbEmployeeIdentityDetails



if we try to insert identity details for same EmployeeId which already exists in the tbEmployeeIdentityDetails table it will show "Violation of PRIMARY KEY constraint" error

INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('5543 4532 1123',2);


if we try to insert identity details for the an EmployeeId which does not exists in the tbEmployee table it givens Foreign key violation error
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('8157 8789 4987',9);


if we try to insert existing aadhar number for an EmployeeId it will show "Violation of UNIQUE KEY constraint" erro
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987',4);
Webcodeexpert.com 9-Apr-19 8:35am
   
.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.190518.1 | Last Updated 19 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100