I am storing identity value of one table to another table in the foreign key but when trying to create a relationship, I get an error 332.
ALTER proc [dbo].[spRegisterUser] @Doj date, @UserName nvarchar(100), @Password nvarchar(10), @Reference nvarchar(50), @Aadhar nvarchar(50), @Email nvarchar(50) AS BEGIN Declare @count int Declare @ReturnCode int Select @count = COUNT(UserName) from tblUsers where UserName = @UserName if @count > 0 Begin Set @ReturnCode = -1 End Else Begin Set @ReturnCode = 1 insert into tblUsers (DoJ,UserName,Password,Reference,Aadhar,Email) Output inserted.User_ID into tblUserProfiles(UserID) values (@DoJ,@UserName,@Password,@Reference,@Aadhar,@Email) End Select @ReturnCode as ReturnValue end
The target table 'tblUserProfiles' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_tblUserProfiles_tblUsers'.
How to overcome this problem? I need PK FK relationship and I also want FK value automatically saved in the column.
Please find a solution for me. Thanks
Since this stored procedure is really only ever inserting one single row of data, you could use this code:
declare @NewUserID INT insert into tblUsers (DoJ, UserName, Password, Reference, Aadhar, Email) values (@DoJ, @UserName, @Password, @Reference, @Aadhar, @Email); -- get the newly created ID for the new user SELECT @NewUserID = SCOPE_IDENTITY(); -- insert into the other table INSERT INTO dbo.tblUserProfiles (UserID) VALUES (@NewUserID);
This assumes that the
User_ID column in the
tblUsers table is in fact an Identity column.