SQL Server - create databases, tables and relationships
Part three 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
  3. Creating Relationships or Joins between Tables (this blog)
  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 Relationships or Joins between Tables

If you've been following this blog, you should now have the following two tables:

Two tables created

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 tblPerson table  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:

Inserting database diagram

Right-click on Database Diagrams for the current database, and choose to insert one.

 

What you will often get then is this message:

Database diagram error 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:

Database properties menu

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:

Database properties dialog box

Follow the steps below to find the owner of your database!

The numbered steps above are:

  1. Select Files.
  2. 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:

Changing owner of database

Click on the build button shown to change the database owner.

You now have two obvious choices for the owner to use:

The SQL Server sa account The Windows account in SQL Server
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:

Checking database owner name

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:

-- set SA account to be owner of database

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:

Create database diagram menu

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:

Choosing tables for a database diagram

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:

Initial database diagram Tidied up database diagram
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:

Creating relationship

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:

New relationship dialog box

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:

Delete and Update Rules

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:

Relationship between two tables

The relationship shows as a line.

 

You can now have great fun altering how this line looks:

Spaghetti relationship

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:

Naming a database diagram

Here we've called our diagram Organisational Structure.

Finally, you can confirm that you want to make changes to all the affected tables:

Changing tables warning

Confirm that you want to change the two tables concerned.

You now have a database diagram, whose structure you can change at any time:

Database diagrams category

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:

Design menu for a table

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:

Relationships tool

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:

Relationship name window

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.

This blog has 0 threads Add post