CODEWITHSUNDEEP
sql
someguy
someguy

Reputation: 1035

Foreign Key is null when insert using Stored Procedure

I've created a insert stored procedure with two tables like in the exapmle:

Table NameAge

CREATE TABLE [dbo].[Assignment3_NameAge]
(
userID int PRIMARY KEY IDENTITY(1,1),
Name varchar(255) NOT NULL,
Age int NOT NULL
)

Table Hobbies

CREATE TABLE [dbo].[Assignment3_Hobbies]
(
hobbiesID int Identity(1,1) Primary Key,
userID int Foreign Key references Assignment3_NameAge(userID),
hobbies varchar(255) NOT NULL,
)

Insert Stored Procedure 

CREATE PROCEDURE [dbo].p_Assignment3Join_ins
        @Name             nvarchar(100),
        @Age              int,
        @Hobbies          nvarchar(100)                   
AS
INSERT INTO [TABLE].[dbo].[Assignment3_NameAge]
           ([Name]
           ,[Age])
VALUES (@Name,@Age)

INSERT INTO [TABLE].[dbo].[Assignment3_Hobbies]
           ([Hobbies])
VALUES (@Hobbies)

The problem is that when i run the stored procedure the table Hobbies has a null value for userid(the foreign key)

What am i doing wrong?

Upvotes: 1

Views: 3255

Answers (3)

TehBoyan
TehBoyan

Reputation: 6890

You should provide the key of the Assignment3_NameAge value you want to insert into Assignment3_Hobbies.

If you want the last inserted you can use SCOPE_IDENTITY() from SQL Server(if you're using SQL Server) or equivalent. It will give you the last inserted value from Assignment3_NameAge

Upvotes: 1

OTTA
OTTA

Reputation: 1081

You're not supplying a value for it, SQL won't automagically fill the value in for you even though you've created a Foreign Key relationship. It's your job to populate the tables.

Upvotes: 1

Tevo D
Tevo D

Reputation: 3381

I am guessing this is SQL Server based on the IDENTITY column. Correct?

The first insert creates a user, but there is no user ID being set on the insert of the hobby. You need to capture the identity value from the first insert to be used in the second insert. Have you gon over the system functions available?

Upvotes: 1

Related Questions