Click here to Skip to main content
15,895,557 members

Doubt in 1-1 relationship in table

Webcodeexpert.com asked:

Open original thread
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
Tags: SQL Server

Plain Text
ASM
ASP
ASP.NET
BASIC
BAT
C#
C++
COBOL
CoffeeScript
CSS
Dart
dbase
F#
FORTRAN
HTML
Java
Javascript
Kotlin
Lua
MIDL
MSIL
ObjectiveC
Pascal
PERL
PHP
PowerShell
Python
Razor
Ruby
Scala
Shell
SLN
SQL
Swift
T4
Terminal
TypeScript
VB
VBScript
XML
YAML

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. 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.
  4. 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.
Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900