Foreign Keys Example – Inserting and Deleting Records

Summary of Video

Using the PersonsDatabase example demonstrates the SQL and stored procedures techniques necessary to insert and delete records in a database containing foreign keys.

Prerequisites

Lecture – Foreign Keys Example 1 – The table designs are from this video.

Case Study – Registration Database – This uses the Registration Database case study.

You should have some familiarity with creating stored procedures.

Video Link

Support Materials

Stored Procedure sp_InsertNewPerson

USE PersonDatabase
GO
CREATE PROCEDURE sp_InsertNewPerson
@PrefixText varchar(200),
@FirstNameText varchar(200),
@LastnameText varchar(200),
@SuffixText varchar(200),
@StreetNumber varchar(20),
@Street varchar(200),
@City varchar(200),
@State varchar(2),
@Zipcode  varchar(10)
AS
BEGIN
INSERT dbo.Persons DEFAULT VALUES
DECLARE @PersonPK INT
SET @PersonPK= @@Identity
INSERT INTO [PersonDatabase].[dbo].[Names]
([PersonID]
,[PrefixText]
,[FirstNameText]
,[LastNameText]
,[Suffixtext])
VALUES
(@PersonPK
,@PrefixText
,@FirstNameText
,@LastNameText
,@SuffixText)
INSERT INTO [PersonDatabase].[dbo].[Addresses]
([PersonID]
,[StreetNumber]
,[Street]
,[City]
,[State]
,[Zipcode])
VALUES
(@PersonPK
,@StreetNumber
,@Street
,@City
,@State
,@Zipcode)
END

Stored Procedure sp_DeletePerson

CREATE PROCEDURE sp_DeletePerson
@PersonID INT
AS
BEGIN
DELETE FROM Names WHERE PersonID = @PersonID
DELETE FROM Addresses WHERE PersonID = @PersonID
DELETE FROM Persons WHERE id = @PersonID
END