SQL: Data Definition Language (DDL) [Part I]

This is part I 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

I am working on a fitness website currently and figured I should share some things from the SQL we are using that are good teaching points! Data Definition Language (DDL) will build and modify the structure of tables/objects in a database and it is available instantly. A data dictionary contains all the information of the objects.

Really, really basic, but don't ever forget this:
A COLUMN goes up and down, like a Greek/Egyptian/Roman column. :D

A ROW goes sideways because you ROW a boat from the side (perpendicular to the boat) to move. Rowing up and down would get you nowhere.

Honestly the main functions here are pretty self explanatory (which is GREAT!):

• CREATE - create objects in database
• RENAME - rename object
• COMMENT - add comments to data dictionary
• ALTER - alters database structure
• DROP - delete objects from database

USE master
GO
 
-- Delete the Fitness database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='Fitness')
BEGIN
    DROP DATABASE Fitness
END

If you're unsure if you already created the database, or if you want to start it fresh.. put this at the beginning. This will clear out an existing

-- Create a new database called Fitness.
CREATE DATABASE Fitness
GO

This actually creates the database.

-- Now that we have created the Fitness Databse, Use it
USE Fitness
GO
 
-- Create the Recipe Table
CREATE TABLE dbo.Recipes
(
    RecipeID int IDENTITY PRIMARY KEY NOT NULL,
       Title NVARCHAR(100) NOT NULL,
       Ingredients NTEXT NULL,
       Directions NTEXT NULL
)
GO
 
-- Create the Recipe Photo Table
CREATE TABLE dbo.RecipePhotos
(
       RecipePhotoID int IDENTITY PRIMARY KEY NOT NULL,
       FilePath VARCHAR(255) NOT NULL,
       RecipeId int FOREIGN KEY REFERENCES Recipes(RecipeID) NOT NULL
)
GO

Note the word IDENTITY in here. This will allow the database to automatically add the unique key each row added to the database. If you do not include it, you will have to add in the "RecipeID" as a column too. This will be described more in later parts of this small SQL example.