Quick Start Guide to SQL Server 7 -- Part 2, Page 4
Relationships in a database allow you to define how information in one table links to information in others.
This is very easy using Microsoft Access, and not that much more difficult with SQL Server, despite a few changes in terminology.
Let's quickly define the two main types of relationship:
- One-to-One In this type of relationship, you say that an entry in one table can only have one entry in another table. For example, an entry in the Employees table may only be allowed one entry in the CompanyCars table
- One-to-Many In this type of relationship, you say that an entry in one table can have many different entries in another. For example, an entry in the GeneralOrder table may have many related entries in the IndividualOrderItems table.
You typically make these relationships work by giving each record a unique value of some sort. For example, say you run a vetinary hospital. You'd probably have a Customer table, containing your customer name and addresses, along with a unique CustomerID number.
You might also have a Pets table, containing the name and breeds of each individual pet the hospital deals with. You'd probably also throw a CustomerID field into that Pets table, so you can tell which customer owns which pet.
Adding relationships to your table ensures data integrity. If we didn't add any relationships to the above scenario, something might go wrong and a particular Pet record might be given an invalid CustomerID. Relationships protect that from happening. They could also stop someone accidentally deleting a Customer that has related Pet records.
In this case, you might consider adding a one-to-many relationship between the main Customer table and the Pets table.
In other words, the relationship is between the CustomerID field in the Customer table (the 'one' part of the relationship) and the CustomerID field in the Pets table (the 'many' part of the relationship).
Then if you tried to add a pet with a CustomerID that didn't exist in the Customer table, the record would be rejected. And these same rules could also stop customers from being deleted when they still have entries in the Pets table.
In brief once more; relationships help maintain data integrity.
For more information about relationships, check out the coverage in part 5 of our VB Database Tutorial here.
To create a relationship:
- Ensure both the tables you wish to join have the necessary keys and are of the same base data type
- A 'primary key' ensures there are no duplicates in a particular field
- If you're creating a one-to-many relationship, the main table field(s) needs to be a primary key (eg, CustomerID)
- If you're creating a one-to-one relationship, the field(s) from both tables involved need to be primary keys
- To add a primary key, edit your table design, highlight the field(s) that you wish to change to primary key status, then click the key image on the toolbar
- View your database in Enterprise Manager (as before)
- Right-click on 'Diagrams' and select 'New Database Diagram...'
- A Database Diagram in SQL Server is like the Relationships screen in Access
- Cancel the Wizard
- Click the 'Add Table' button on the toolbar
- Add the desired tables, then hit Close
- You may find the tables have hidden themselves under each other. To view properly, simply drag them apart
- Highlight the field(s) you want to be involved in the relationship from the main table
- By clicking the small box to the left of the field
- Drag and drop the adjacent box on the field you want to create the relationship with, in the secondary table
- In the box that appears, check the relationship parameters, then click OK
- You will typically have one field from the primary key table, another from the foreign key table
- It is recommended you accept the default relationship options
- Hit ALT-F4 to close the Database Diagram
- Give the diagram a name, then click OK
- If you are given warnings stating existing data violates the relationships you attempted to put in place, sort it out
- If prompted that the specified tables will be saved to your database, click Yes to continue
Page 4 of 5