I store the identity value of a table in another table of the foreign key, but when I do a relationship, it gives me an error

advertisements

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

Error is:

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.