Creating Relationships or Joins between Tables
If you've been following this blog, you should now have the following two
tables:
You should have a
tblCompany and a tblPerson table.
What we now need to do is to tell SQL Server that the CompanyId column in the
tblPerson table for any person must match up to one of the values
for the CompanyId field in the tblCompany
table!
 |
 |
|
The company id here ...
|
... must match the id here. |
You could argue that SQL Server should guess the relationship above, but SQL
Server can be like one of those irritating people who need everything spelt out
formally (it lacks common sense sometimes!).
Creating a Database Diagram - Start
The easiest way to create a relationship is to drag a column from one table
onto another, using a database diagram. To create one of these,
just right-click:
Right-click on
Database Diagrams for the current database, and choose to insert one.
What you will often get then is this message:
This error message often appears when you try to add a database diagram.
The problem is usually that you're using Windows authentication to log into your
database, and the owner of the database doesn't have a valid log-in account
assigned.
There are two ways to log on to a SQL Server database. One is to
specify a valid name and password (SQL Server authentication); the other is to
rely on your Windows log-on giving you sufficient rights to view the SQL Server
database also (Windows authentication).
Changing who Owns your Database
To solve the problem above (if you have it - if not, ignore this section)
first look at the properties of your database:
Right-click on your database and choose to show its properties.
You can now see who the database's owner is by following the steps below:
Follow the steps below to find the owner of your database!
The numbered steps above are:
- Select Files.
- You can now see the owner's name.
In this case the owner of the database is the person with Windows logon id
DOMAIN_NAME\Basil.Brush. You can now change this:
Click on the build button shown to change the database owner.
You now have two obvious choices for the owner to use:
|
|
|
The sa account |
The Windows account |
In my experience either will work, although it's a good idea to check the
name you've entered:
Click on the button shown to check you've entered a valid account name.
You should now be able to continue to create a valid database diagram. If
the above change makes you uneasy, you can always create the database diagram
and then revert back to the previous user.
You can even change accounts using an SQL query:
ALTER AUTHORIZATION ON DATABASE::BlogExample TO sa
GO
I wouldn't claim to be an expert on SQL Server security, but the above steps
work for me. Googling suggests that this won't have any effect on the
security of your database, but you shouldn't take my word for this!
Creating the Database Diagram - Finishing the Job
Now that you can create a database diagram, do so:
Right-click on
Database Diagrams and choose to add a new one.
Choose the tables that you want to link, then click on the Close
button:
Here we're linking the two tables in our database.
If you have a complex database, you could have several different database
diagrams, each showing the relationships for a particular part of your database.
You can now tidy up your diagram by dragging tables round:
|
|
|
How it first appears. |
How the final version might look.
|
Creating a Relationship
To link two tables formally, drag a column from one table onto the matching
column in the other:
To create our relationship:
You will usually only create a relationship between two columns if they both
have the same data type (here both are bigint fields), and if
one of the two columns is the primary key in its table.
SQL Server will show a dialog box asking you to confirm that you've linked
the correct two fields, and giving your new relationship a name:
The two tables are called the primary key table and the foreign
key table.
I wouldn't recommend renaming the
relationship. The name has to be unique within the database, and the
default name chosen by SQL Server does a pretty good job of ensuring that this
will always be the case.
As already explained, the CompanyId column is the primary
key in the tblCompany table. In the tblPerson
table it is known as the "foreign key", since it looks up information from a
different table.
Choosing Cascade Update and Cascade Delete Options
This topic is discussed in more detail in my earlier blog on database design,
but I'll go through it again here.
If you expand the INSERT and UPDATE Specification category
which appears when you choose OK in the dialog box above,
you'll see a Delete Rule and an Update Rule
appear. Each of these has 4 choices, as shown below:
There are four choices for each of the two rules, as shown below.
The rules govern what should happen when you delete or change rows:
|
Rule
|
What it controls |
|
Delete Rule |
When you delete a parent record (in this case, a company), what should happen to
all of its associated children (in this case, the people who work for the
company)? |
|
Update Rule |
When you change a parent company's CompanyId number, what
should happen to all of the child people records having the same company id? |
The options are as follows:
|
Action |
What it means if you delete a company |
|
No Action |
The orphan children (the people working for the company) will be left in the
database (so there will be people records who have a company id which doesn't
exist in the companies table). This is a bad idea! |
|
Cascade |
SQL Server will automatically delete all the people who worked for the company.
Sound a bad idea? The answer is not to delete the company in the first
place! |
|
Set Null |
The CompanyId column will be set to Null for
all of the people who worked for the now deleted company. |
|
Set Default |
The CompanyId column will be set to the default value for each
person who worked for the now deleted company (provided that a default value has
been set). |
To me the best option has always seemed to be Cascade - if
you delete a company, you'll probably want to delete any people attached to it.
However, all of the choices (apart from the first one) make sense.
The Final Relationship
When you select OK again, you should now have a link between
your two tables:
The relationship shows as a line.
You can now have great fun altering how this line looks:
Clearer now?
The most unforgivable feature of any Microsoft software application? As you
move tables around in a database diagram, relationships don't appear to join the
correct two columns (as in the example above, where the line appears to join the
CompanyId column in the companies table to the
FirstName column in the person table). If Microsoft can get this
right in Access, why not in SQL Server?
When you save the database diagram, you'll be prompted to give it a name:
Here we've called our diagram
Organisational Structure.
Finally,
you can confirm that you want to make changes to all the affected tables:
Confirm that you want to change the two tables concerned.
You now have a database diagram, whose structure you can change at any time:
Our new database diagram shows up in the list.
Another Way to Create, View and Edit Relationships
Using database diagrams is the easiest way to create relationships, but there
is another way - you can edit the design of either table involved:
Right-click on either table and choose to change its design.
With the table now open for viewing and design changes, choose to view its
relationships:
Click on this tool to view the relationships for the open table.
You can now view and edit the details for each relationship that
you've created for the table:
You can change the name of a relationship, which columns it affects, etc.
When you've finished, just close down the dialog box to save your
changes. You've now completed the creation of your tables and database,
although for the sake of completeness the final part of this blog series shows
how to create an index, to speed up the database.
