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:
Any tables that you create will appear in this list.
To create a new table, just right-click:
Right-click on
Tables to create a new one.
SSMS will produce a 3-column grid for you to fill in:
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:
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:
First select the
CompanyId column.
You can now tell SQL Server to increment its value automatically:
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:
|
|
|
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:
Click on this icon to close a table.
You can now choose that you want to save the table:
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:
Type in a name for your new table, and select OK.
Your new table should appear in the list of tables for this database:
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:
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:
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:
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:
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!
