Insert auto increment primary key into foreign key table

Question

I have 2 tables User and UserLogin. UserLogin have a foreign key relationship with User table. What I want to do here is whenever I insert data into the User table through my API their autogenerated(user_id) auto-inserted into UserLogin table.

User table:

user_id | user_name | user_email

UserLogin table:

user_id | user_password | user_number

So when I run my query to add name and email in User table then autoincremented user_id is automatically inserted in UserLogin table with the provided password and number. How can I achieve this and is that thread safe?

Possible duplicate. https://stackoverflow.com/questions/560783/the-equivalent-of-sqlserver-function-scope-identity-in-mysql — SS_DBA, Oct 10 '17 at 13:06

score 0 · Answer 1 · answered Oct 10 '17 at 12:47

0

yes it is possible and usually can be optained by @@identity try something like

 set nocount off;
insert into User Values("Name","Email")
declare @lastID = @@identity
insert into UserLogin values(@lastID,"Password","number")

answered Oct 10 '17 at 12:47

RAHUL S R

1,569
1
11
20

`SCOPE_IDENTITY()` would probably be safer and more reliable to use than `@@IDENTITY`. https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql – SS_DBA Oct 10 '17 at 12:58
yes scope is better but i beleive IDENT_CURRENT("table name") is the best one it will limit the scope to specific table while scope_identity and @@identity will check for the largest generated id with difference scope will scope upto the current procedure or tsql – RAHUL S R Oct 11 '17 at 16:07

score 0 · Answer 2 · answered Oct 10 '17 at 13:35

This code helps you

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
    [user_id] [int] IDENTITY(1,1) NOT NULL,
    [user_name] [varchar](100) NULL,
    [user_email] [varchar](100) NULL,
    [salt] [uniqueidentifier] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserLogin](
    [UserLoginId] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NULL,
    [user_password] [binary](1) NULL,
    [user_number] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_UserLogin_User] FOREIGN KEY([user_id])
REFERENCES [dbo].[User] ([user_id])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_UserLogin_User]
GO

 CREATE PROC [dbo].[Usp_UserLogin] 
 (
 @user_name VARCHAR(100)
 ,@user_email VARCHAR(100)
 ,@user_password VARCHAR(200)
 ,@user_number INT
 )

 AS
 Begin
  SET NOCOUNT ON 
 DECLARE @Salt UNIQUEIDENTIFIER =NEWID() 
        ,@IdentityNUmber INT
        ,@responseMessage nvarchar(1000)

 BEGIN TRY

        INSERT INTO Dbo.[User]([user_name],[user_email],[salt])

       SELECT        @user_name
                    ,@user_email
                    ,@salt



        SET @IdentityNUmber=SCOPE_IDENTITY()


        INSERT INTO Dbo.[UserLogin]([user_id],[user_password],user_number)

        SELECT  
         @IdentityNUmber
        ,@user_number 
        ,HASHBYTES('SHA2_512', @user_password + CAST(@salt AS NVARCHAR(36))) 



 END TRY

  BEGIN CATCH 
          SET @responseMessage=ERROR_MESSAGE() 
      END CATCH 



 END
GO

Execute the Procedure

EXEC [Usp_UserLogin] @user_name='Test1',@user_email='Test1@gmail',@user_password='Test1@123',@user_number=2

Insert auto increment primary key into foreign key table

2 Answers2