| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Lecture - Foreign Keys Example 2

Page history last edited by Dr. Ron Eaglin 11 years, 4 months ago
 

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

 

http://online1.daytonastate.edu/player2.php?id=70c445ee64b1ed0583367a12a79a9ef2 

 

 

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 
   
   

 

 

 

All Materials Copyright 2012 Dr. Ron Eaglin

Comments (0)

You don't have permission to comment on this page.