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.

Table relationship

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
one to one
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
one-to-one-violation

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]
                
one-to-one-morevalidation
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. one-to-one-index-on-aadhar-number
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                   
                    
one-to-one-insert-stored-procedure

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
            
one-to-one-insert-stored-duplicate-procedure

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
            
one-to-one-insert-stored-procedure-validation