Tuesday, June 24, 2014

SQL: DML continued & Foreign Keys (parent/child tables) [Part III]

This is part III of a set of SQL notes I wanted to post about my current fitness website project.

These are the links for each part to navigate:

Part I | Part II | Part III | Part IV

Here I am making use of inserting, deleting, and updating things in the database. Once something is inserted, you must either delete or update it-- you cannot insert something already inserted (it will just become duplicated). On the insert tag, note how order does not matter here for the column values, however, you must match the 1st column to the 1st piece of data. Title goes with 'chicken'. Directions goes with 'fry with olive oil', and Ingredients goes with 'chicken, olive oil'. As long as they are paired, it is ok to mix otherwise. Below I annotated the others within the SQL.

INSERT dbo.Recipes (Title, Directions, Ingredients)
	VALUES ('Chicken', 'Fry with olive oil', 'chicken, olive oil')
GO

INSERT dbo.RecipePhotos (FilePath, RecipeId)
	VALUES ('http://mysite.com/recipe1.jpeg', 1)
GO

INSERT dbo.Recipes (Title, Directions, Ingredients)
	VALUES ('Salmon Salad', 'Add lettuce, tomato, and cucumbers. Mix.', 'salmon, lettuce, tomato, cucumbers')
GO

INSERT dbo.Recipes (Title, Directions, Ingredients)
	VALUES ('Tilapia Soup', 'Add soup. Add tilapia.', 'tilapia, soup')
GO

--To delete one, tell it WHERE you want it to be deleted from!
DELETE FROM dbo.Recipes
	WHERE RecipeID=4;
GO

--To delete multiple, you must use an or statement
DELETE FROM dbo.Recipes
	WHERE RecipeID=5 or RecipeID=6;
GO

UPDATE dbo.Recipes
    SET Directions = 'Make the soup. Add tilapia on top of soup.'
    WHERE RecipeID = 3
GO

--Deletes everything in this database
DELETE FROM dbo.RecipePhotos
GO

--Deletes everything in this database
DELETE FROM dbo.Recipes
GO

I have 2 databases in this: dbo.Recipes and dbo.RecipePhotos, and the dbo.RecipePhotos is the child to the parent dbo.Recipes because I want to allow users to choose as many photos as they would like (more on part IV if the user chooses to not upload any and how to avoid a null/mental breakdown/implosion of your work). If you look at the leftmost column of my screen you can see how both db are shown and that there is the unique key for the main dbo.Recipes, and how the dbo.RecipePhotos has an FK = foreign key. The fact this dbo.RecipePhotos carries the foreign key means it is the referencing/child table.. and thus making the table with the candidate/matching key the parent table that is being referenced.

I want you to notice recipe 1 has 3 photos, recipe 2 has 2 photos, recipe 3 has 1 photo, and recipe 4 has no photos. All of these are acceptable based on how this database has been put together. Also notice how the recipeID is referenced (lower table, very rightmost column).. this is the foreign key that the child here uses to link to the parent.