Using the PersonsDatabase example demonstrates the SQL and stored procedures techniques necessary to insert and delete records in a database containing foreign keys.
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.
http://online1.daytonastate.edu/player2.php?id=70c445ee64b1ed0583367a12a79a9ef2
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