CODEWITHSUNDEEP
sql-servert-sql
JMeterX
JMeterX

Reputation: 438

Foreign Key Constraint in Stored Procedure - SQL Server

I have the following stored procedure, I have two tables here, Movie and Director. Both need to be updated when a new movie has been created or added. How do you handle FKs in stored procedures? The FK in this case is director_id. It is a primary key in Director but a FK in Movie Do I need to specify it twice like so? I am getting conflict errors

CREATE PROCEDURE Book_Book_Creation
    @Book_id_arg DECIMAL(12),    
    @author_id_arg DECIMAL(12),    
    @type_id_arg DECIMAL(12),     
    @title_arg VARCHAR(64),      -
    @copyright_arg DECIMAL(4),   
    @dauthor_id_2_arg DECIMAL(12),    
    @author_fname_arg VARCHAR (64), 
    @author_lname_arg VARCHAR (64)  
AS
BEGIN 
    INSERT INTO Book(Book_id, author_id,genre_id, title, copyright)
    VALUES (@author_arg, @author_id_arg,  @type_id_arg, @title_arg, @copyright_arg);
    INSERT INTO Author(author_id, author_fname, author_lname)
    VALUES (@director_id_2_arg, @director_fname_arg, @director_lname_arg)
END;

EXECUTE Book_Book_Creation 32,32,1,'Tempting',2013,32,'Taylor','Mendez';

Upvotes: 1

Views: 7760

Answers (2)

Devart
Devart

Reputation: 121912

Try this one -

ALTER PROCEDURE dbo.Movie_Movie_Creation12

    @movie_id_arg DECIMAL(12),  
    @director_id_arg DECIMAL(12),
    @genre_id_arg DECIMAL(12), 
    @title_arg VARCHAR(64),
    @copyright_arg DECIMAL(4),  
    @director_fname_arg VARCHAR (64), 
    @director_lname_arg VARCHAR (64) 

AS BEGIN 

    INSERT INTO dbo.Director (Director_id, Director_fname, director_lname)
    SELECT @director_id_arg, @director_fname_arg, @director_lname_arg

    INSERT INTO dbo.Movie (Movie_id, director_id,genre_id, title, copyright)
    SELECT @movie_id_arg, @director_id_arg,  @genre_id_arg, @title_arg, @copyright_arg

END

EXECUTE dbo.Movie_Movie_Creation12  
            @movie_id_arg = 32 
        ,   @director_id_arg = 32
        ,   @genre_id_arg = 1
        ,   @title_arg = 'Argo'
        ,   @copyright_arg = 2012
        ,   @director_fname_arg = 'Ben'
        ,   @director_lname_arg = 'Affleck'

Upvotes: 0

marc_s
marc_s

Reputation: 754408

Basically, you just need to do this:

  • insert into the Director table first
  • get the newly inserted ID from that table (assuming that the Director_Id column is your primary key and is of type INT IDENTITY)
  • then insert into the Movie table with that new ID

Something like this:

DECLARE @NewDirectorID INT

INSERT INTO Director (Director_id, Director_fname, director_lname)
VALUES (@director_id_2_arg, @director_fname_arg, @director_lname_arg)

SELECT @NewDirectorID = SCOPE_IDENTITY()

INSERT INTO Movie (Movie_id, director_id,genre_id, title, copyright)
VALUES (@movie_id_arg, @NewDirectorID,  @genre_id_arg, @title_arg, @copyright_arg);

I don't see why you would pass in the director's ID as a parameter - twice!

Upvotes: 6

Related Questions