What is one to one relationship in database?
One to one is a relationship between two tables where each primary table record should be associated with zero or one secondary table record.
David is a citizen of India.
He can have only one Permanent Account number(PAN). Once he gets the PAN from government authority, he can't apply for PAN to get one more PAN. If they apply one more time, the government authority team will reject the application.
The same way, he can't get more than one Aadhar card, Passport, Ration card, EPF Account(Employees' Provident Fund)
David doesn't have EPF account now. He may have in feature. He cannot have more than one EPF account.
How to implement this scenario in database level?
- Step 1. Create the UserMaster table.(Primary/Master table).
-
CREATE TABLE [dbo].[UserMaster]( [UserId] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](100) Not NULL, [LastName] [varchar](100) NULL, [DateOfBirth] [datetime] Not NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )) - Step 2. Create the Aadhar Table(Secondary table).
-
CREATE TABLE [dbo].[AadharMaster]( [Id] [int] IDENTITY(1,1) NOT NULL, [AadharNumber] [varchar](100) Not NULL, [IssueDate]DateTime PRIMARY KEY CLUSTERED ( [Id] ASC )) - Step 3. Create the Foreign key relationship between these two tables.
-
ALTER TABLE [dbo].[AadharMaster] ADD CONSTRAINT FK_AadharMaster FOREIGN KEY (UserId) REFERENCES [dbo].[UserMaster]([UserId]); - Step 4. Create the Unique index on "UserId" column in secondary table.
-
CREATE UNIQUE INDEX IDXU ON [dbo].[AadharMaster] (UserId);
Validating the tables
- Step 1 : Insert the David's information in "UserMaster" table
Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values('David','J','1990-05-25')
- Step 2 : Insert the David's Aadhar information in "AadharMaster" table
Insert into [dbo].[AadharMaster]([AadharNumber],[IssueDate],[UserId])Values('250485478985','2021-01-10',1)
- Verify the data
-
- Step 3 : Insert the David's Aadhar information in "AadharMaster" table once again.
Insert into [dbo].[AadharMaster]([AadharNumber],[IssueDate],[UserId])Values('250485478985','2021-01-10',1)
- Verify the data
-
We have implemented the one to one relationship with SQL tables.
- Insert some more user data
-
Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values('Ramkumar','M','1995-10-14') Select * from [dbo].[UserMaster] Insert into [dbo].[AadharMaster]([AadharNumber],[IssueDate],[UserId])Values('980400278775','2021-01-10',2) Select * from [dbo].[UserMaster] Select * from [dbo].[AadharMaster] -
- Aadhar number is unique across the country.
-
The data accuracy and consistency purpose, we have to create a unique index on "AadharNumber" column in "[dbo].[AadharMaster]" table.
CREATE UNIQUE INDEX IDXU_AadharMaster_AadharNumber ON [dbo].[AadharMaster] (AadharNumber);
We cannot insert same Aadhar number for more than one user.
- Insert data using stored procedure and mapping user Id between [dbo].[UserMaster] and [dbo].[AadharMaster]
-
Create Procedure InsertUserData( @FirstName varchar(100), @LastName varchar(100), @DateOfBirth DateTime, @AadharNumber varchar(12), @IssueDate DateTime) as Begin Declare @UserId int; Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values(@FirstName,@LastName,@DateOfBirth) Set @UserId = SCOPE_IDENTITY() Insert Into [dbo].[AadharMaster]([AadharNumber],[IssueDate],[UserId]) Values(@AadharNumber,@IssueDate,@UserId) End GO Exec [dbo].[InsertUserData] 'David','J','1990-05-25','250485478985' GO Select * from UserMaster u left join AadharMaster a on u.UserId = a.UserId GO
I have followed the same way for PAN card, Passport details and find complete sql script.
CREATE TABLE [dbo].[UserMaster](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NULL,
[DateOfBirth] [datetime] NOT NULL,
CONSTRAINT PK_UserMaster_UserId PRIMARY KEY (UserId)
)
GO
CREATE TABLE [dbo].[AadharMaster](
[AadharId] [int] IDENTITY(1,1) NOT NULL,
[AadharNumber] [varchar](12) NOT NULL,
[IssueDate] [datetime] NOT NULL,
[UserId] [int] NOT NULL,
CONSTRAINT PK_AadharMaster_AadharId PRIMARY KEY (AadharId)
)
GO
ALTER TABLE [dbo].[AadharMaster] WITH CHECK ADD CONSTRAINT [FK_AadharMaster_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserMaster] ([UserId])
GO
CREATE UNIQUE INDEX IDXU_AadharMaster_UserId ON [dbo].[AadharMaster] (UserId);
GO
CREATE UNIQUE INDEX IDXU_AadharMaster_AadharNumber ON [dbo].[AadharMaster] (AadharNumber);
GO
CREATE TABLE [dbo].[PANInfo](
[PANId] [int] IDENTITY(1,1) NOT NULL,
[PAN] [varchar](10) NULL,
[UserId] [int] NULL,
CONSTRAINT PK_PANInfo_PANId PRIMARY KEY (PANId)
)
GO
ALTER TABLE [dbo].[PANInfo] WITH CHECK ADD CONSTRAINT [FK_PANInfo_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserMaster] ([UserId])
GO
CREATE UNIQUE INDEX IDXU_PANInfo_UserId ON [dbo].[PANInfo] (UserId);
GO
CREATE UNIQUE INDEX IDXU_PANInfo_AadharNumber ON [dbo].[PANInfo] (PAN);
GO
CREATE TABLE [dbo].[PassportInfo](
[PassportId] [int] IDENTITY(1,1) NOT NULL,
[PassportNumber] [varchar](12) NULL,
[ExpiryDate] [datetime] NULL,
[UserId] [int] NULL,
CONSTRAINT PK_PassportInfo_PassportId PRIMARY KEY (PassportId)
)
GO
ALTER TABLE [dbo].[PassportInfo] WITH CHECK ADD CONSTRAINT [FK_PassportInfo_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserMaster] ([UserId])
GO
CREATE UNIQUE INDEX IDXU_PassportInfo_UserId ON [dbo].[PassportInfo] (UserId);
GO
CREATE UNIQUE INDEX IDXU_PassportInfo_PassportNumber ON [dbo].[PassportInfo] (PassportNumber);
GO
ALTER Procedure [dbo].[InsertUserData](
@FirstName varchar(100),
@LastName varchar(100),
@DateOfBirth DateTime,
@AadharNumber varchar(12),
@AadharIssueDate DateTime,
@PANNo varchar(10),
@PassportNo varchar(12),
@PassportExpiryDate DateTime
)
as
Begin
Declare @UserId int;
If(@FirstName!='')
Begin
Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values(@FirstName,@LastName,@DateOfBirth)
Set @UserId = SCOPE_IDENTITY()
Insert Into [dbo].[AadharMaster]([AadharNumber],[IssueDate],[UserId]) Values(@AadharNumber,@AadharIssueDate,@UserId)
Insert into [dbo].[PANInfo]([PAN],[UserId]) Values(@PANNo, @UserId)
Insert into [dbo].[PassportInfo]([PassportNumber],[ExpiryDate],[UserId])Values(@PassportNo,@PassportExpiryDate,@UserId)
End
End
GO
Exec [dbo].[InsertUserData] 'David','J','1990-05-25','250485478985','2021-01-10','BP345DFG12','EX3456756','2025-05-12'
GO
Select * from [dbo].[UserMaster] u
left join [dbo].[AadharMaster] a on u.UserId = a.UserId
left join [dbo].[PANInfo] pan on u.UserId = pan.UserId
left join [dbo].[PassportInfo] pp on u.UserId = pp.UserId
Exec [dbo].[InsertUserData] 'Rajesh Kumar','J','1989-05-25','250485478985','2021-01-10','BP345DFG12','EX3456756','2025-05-12'
GO
Given below script validate the existence of Aadhar card number, PAN, Passport number and then insert the record. The SQL server will not raise the exception.
Create Procedure [dbo].[InsertUserDataWithValidation](
@FirstName varchar(100),
@LastName varchar(100),
@DateOfBirth DateTime,
@AadharNumber varchar(12),
@AadharIssueDate DateTime,
@PANNo varchar(10),
@PassportNo varchar(12),
@PassportExpiryDate DateTime
)
as
Begin
Declare @UserId int;
If(@FirstName!='')
Begin
Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values(@FirstName,@LastName,@DateOfBirth)
Set @UserId = SCOPE_IDENTITY()
If(@AadharNumber!='' and Not Exists(Select AadharId from [dbo].[AadharMaster] where AadharNumber = @AadharNumber))
Begin
Insert Into [dbo].[AadharMaster]([AadharNumber],[IssueDate],[UserId]) Values(@AadharNumber,@AadharIssueDate,@UserId)
End
If(@PANNo!='' and Not Exists(Select [PANId] from [dbo].[PANInfo] where [PAN] = @PANNo))
Begin
Insert into [dbo].[PANInfo]([PAN],[UserId]) Values(@PANNo, @UserId)
End
If(@PassportNo!='' and Not Exists(Select PassportId from [dbo].[PassportInfo] where [PassportNumber] = @PassportNo))
Begin
Insert into [dbo].[PassportInfo]([PassportNumber],[ExpiryDate],[UserId])Values(@PassportNo,@PassportExpiryDate,@UserId)
End
End
End
GO
Exec [dbo].[InsertUserDataWithValidation] 'Ragesh','Kumar','1998-05-25','250485478985','2021-01-10','BP345DFG12','EX3456756','2025-05-12'
GO
Select * from [dbo].[UserMaster] u
left join [dbo].[AadharMaster] a on u.UserId = a.UserId
left join [dbo].[PANInfo] pan on u.UserId = pan.UserId
left join [dbo].[PassportInfo] pp on u.UserId = pp.UserId
GO
