SQL Server - create databases, tables and relationships
Part two of a four-part series of blogs

A guide to how to create databases in SQL Server Management Studio, including creating tables, indexing columns and using database diagrams to create relationships between tables.

  1. Creating a Database and Tables in SQL Server
  2. Creating Tables within a Database (this blog)
  3. Creating Relationships or Joins between Tables
  4. Indexing Columns in a SQL Server Table

This blog is part of the Wise Owl online SQL tutorial.  We also run classoom-based courses in SQL.

Posted by Andy Brown on 19 November 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating Tables within a Database

If you expand the database that you have (I hope) just created, you can see that it already contains a Tables category:

System tables category

Any tables that you create will appear in this list.

 

To create a new table, just right-click:

New table menu option

Right-click on Tables to create a new one.

 

SSMS will produce a 3-column grid for you to fill in:

A skeleton SSMS table

The start of a new table.

 

Specifying the Table Columns

Every column that you create in a table has 3 pieces of information that you can fill in:

Information What it contains
Column Name The name of the column or field (don't use spaces).
Data Type The type of data for the column.
Allow Nulls Whether it is possible not to enter a value for this column for a record (so, for example, you would untick this box to make sure that someone fills in a person's name, but leave it unticked, perhaps, for the date of birth column, which can be left blank).

Here is what you would probably create for our example tblCompany table:

Columns for table of companies

The columns would give the unique number of each company, its name and a description of what it is and does.

 

You should refer to this separate blog for the possible data types that you can use.

Identitiy Columns

You now want to make sure that every time someone enters a new company, it is automatically assigned a number one bigger than the previous one (that way, you can be confident no two companies can ever share the same number).  Here's how to do this.  First, make sure you have the id column selected:

The CompanyId column selected

First select the CompanyId column.

 

You can now tell SQL Server to increment its value automatically:

Identity Specification properties

Expand the Identity Specification category, and tell SQL Server that this column is an identity one. You can also specify the increment and seed (although usually you'll set these both to 1 - see hint below for what they mean).

 

Suffer from triskaidekaphobia?  Set the identity seed to 14 and the increment to 2, and your numbers will go 14, 16, 18 ... instead of 1, 2, 3 ... !

Creating a Primary Key

Every table in SQL Server should have a primary key (it's virtually a requirement).  This is usually - although not always - a single column.  The primary key is the field whose value uniquely identifies a record.  Here are some examples:

Table Primary key
National Insurance database National Insurance number
NHS database NHS number
Your company's personnel system Your personnel number

In our example, the primary key is going to be the CompanyId column, so you can right-click on this and set it:

Setting primary key The primary key symbol
Right-click to set the key ... ... to get a key symbol next to it!

 

Saving the Table

Having created all of the columns, set the identity column and set the primary key, you can just close down your table, at which point you'll be asked to name it:

Close icon for table

Click on this icon to close a table.

 

You can now choose that you want to save the table:

Saving a table in SSMS

You'll have a chance to choose a different name if you choose Yes!

It's a convention in SQL Server to prefix table names with tbl, although you don't have to follow this convention:

Choosing table name for SQL Server

Type in a name for your new table, and select OK.

Your new table should appear in the list of tables for this database:

New table created

If you can't see the table you've created, right-click on Tables and choose to refresh the list.

 

The Table of Persons

You could now repeat the steps shown above to create the table of people for our database:

Table of people - design view

The table of people, called tblPerson. The columns are listed below.

 

The columns in this table are:

Heading 1 Heading 2
PersonId The unique number for each person (the primary key, which should be set to an Identity column).
FirstName Each person's first name (we allow this to be empty, or null).
LastName Each person's last name (must be filled in).
DateOfBirth The optional date of birth for each person.
CompanyId The number of the company each person belongs to.

Again, the possible data types you could have used are explained in this separate blog.

Entering Records for a Table

Having created a table, you can right-click on it to enter data for it:

Editing table data

Right-click on a table to edit its data. The menu option shown is for SQL Server 2008 R2; the menu is similar for other versions of SQL Server.

 

Here's what you could type in for our example for the companies table:

Four records in company table

Here we've created 4 companies. SQL Server always shows a blank row for a new record.

 

Note that you can't type in a value for the CompanyId, as this will be generated automatically by SQL Server.  If you don't want this to happen, don't make the column an identity column!

You could now close this table down, although you don't need to - each record is saved as soon as you move off it. 

It's now time to repeat these steps to type in some people into the tblPerson table:

Records in tblPerson table

Our 7 people: 3 work for Shell, 2 for IBM, 1 for Microsoft and 1 for Wise Owl.

Note that you have to know the company ids to enter them in the table (for example, if David Richards works for Shell, you need to know that the CompanyId for Shell is 1, and type this into the tblPerson table against his name).  Most applications will contain a front-end form featuring a drop list to make this easier, so that users don't have to memorise the id numbers for each company!

The Next Stage

Now that you've created two tables, it's time to tell SQL Server that there is a relationship between them (and prevent someone from assigning a company id to a person when no company with that id exists in the parent tblCompany table).  This is the subject of the next part of this blog!

This blog has 0 threads Add post