Doubt in 1-1 relationship in table

Question

0.00/5 (No votes)

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:

SQL

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 19-Mar-19 0:15am

Webcodeexpert.com

Updated 19-Mar-19 10:00am

Herman<T>.Instance

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2019-03-19T00:26:00

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!

Posted 19-Mar-19 0:26am

OriginalGriff

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

Maciej Los · Answer 2 · 2019-03-19T00:47:00

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, ...

MadMyche · Answer 3 · 2019-03-19T10:00:00

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:

SQL

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:

SQL

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

Doubt in 1-1 relationship in table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0