tables are made up of columns and each column needs a data type. We should also indicate whether the column is required, and designate one of our columns as a primary key.

CREATE [TableName] ( ColumnOneName DataType (size) {Requirement} , ColumnTwoName DataType (size) {Requirement} )

 Personal STYLE – I put the comma on the second line because that is how I like to style my code. The create statement could be on one line—we separate the various lines for readability.

CREATE [TableName] ( ColumnOneName DataType(size) {Requirement}, ColumnTwoName DataType(size) {Requirement})

To the database, these two example statements are the same. Formatting makes the code easier to read for us humans.

Let’s imagine we decide to start collecting information in our database on pet owners who have fish as pets. We want to create a new table to capture this information. Our CREATE TABLE statement will look like this:

 

CREATE TABLE Fish

(

FishIdentifier INT

, BirthDateOfFish DATETIME NULL

, BreedOfFish VARCHAR(30) NULL

, ColorOfFish VARCHAR(25) NOT NULL

, MonthsWithOwner INT NULL

, NameOfFish CHAR(40) NOT NULL

, OwnerIdentifier INT NULL

, TankSizeInGallons SMALLINT NULL

, PRIMARY KEY NONCLUSTERED ( FishIdentifier )

) ;

OUTPUT

Command(s) completed successfully.

Executing this statement will create the table, add all of the columns to the table, and make the FishIdentifier column the Primary Key. It will not; however, create an auto sequence number for the primary key and we don’t go into any detail about that option in this book; however, more detail can be found at http://sqldatapartners.com/AutoIncrement. The scripts provided with this book include the creation of the auto increment for the Primary Key. We discussed foreign keys**add link** and the fact that we need to create the Foreign Key. For example, although VeterinarianVisitHistory has a column called FishIdentifier, the Foreign Key has not yet been created. In the database diagram below, notice there is no line between Fish and VeterinarianVisitHistory. This indicates to us a Foreign Key has not yet been created.

Column Requirement

Many database systems do not require you to specify whether the column is required when you create or modify a table. If this is not specified the default is NULL, meaning no value is required. While you might be tempted to make this a habit—you will be surprised how many queries you won’t be able to make because you don’t have enough data. Sometimes requiring information is a good database decision.

Primary Keys columns automatically default to required; however, Foreign Keys have the option of allowing a NULL option. In our database, this would allow us to insert a new row into the dog table without requiring an owner, and this makes sense. On the other hand, having a record in the VeterinarianVisitHistory without an animal doesn’t make any sense, so a requirement to have an animal makes sense when creating that record.

Creating other types of objects

We can also create views, triggers, functions, stored procedures, users and roles. Views are discussed in greater detail in the next lesson. The other objects are considered more advanced topics and are not addressed in this book.

Auto Incrementing Primary Key

This post has more information about how to auto increment your primary key in SQL Server using the IDENTITY keyword.