|
To manage the flow of information from one table (A)
to another table (B), the table that holds the information, A, must make
it available to other tables, such as B. There are various issues that
must be dealt with:
- You must be able to uniquely identify each record from a table (A)
without any confusion. For example, if you create a list of cars on a
table, you should make sure that there is a unique (no duplicate) tag
number for each car because each car should have one and must have one
tag number. This ensures that there are no duplicate records on the
table.
- A table (A) that holds information should make that information
available to other tables (such as B)
- Two tables must not serve the same purpose. Once you have unique
information on each table, one table can make its data available to
other tables that need it so that the same information should not be
entered in more than one table
These problems are solved by specifying a particular
column as the "key" of the table. Such a column is referred to as the
primary key.
In a relational database, which is the case for most
of the databases you will be creating, each table should have at least
one primary key. As an example, a primary key on an car table of a car
rental company can be set on a Tag Number field because each car should
have a unique tag number. A table can also use more than one column to
represent the primary key if you judge it necessary.
Once you have decided that a table will have a primary
key, you must decide what type of data that field will hold. If you are
building a table that can use a known and obvious field as unique, an
example would be the shelf number of a library, you can set its data
type as char or varchar and make it a primary key. In
many other cases, for example if you cannot decide on a particular field
that would hold unique information, an example would be customers
Contact Name, you should create your own unique field and make it the
Primary Key. Such a field should have an int data type.
|
Visually Creating a Primary Key |
|
To create a primary key in the Microsoft SQL Server Management Studio
or Microsoft Visual Studio,
in the table, create a column and specify its data type:
- Then, on the toolbar, click the
Set Primary Key button
- You can also right-click a column and click Set Primary Key
Here is an example:
|
Creating a Primary Key With SQL |
|
To
create a primary column using SQL, the primary thing to do is, on the right side of the column
definition, type PRIMARY KEY. Here is an example:
CREATE TABLE Persons
(
PersonID int identity(1,1) PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL
);
|
The Primary Key Constraint |
|
In the SQL, you can give a specific name
to a primary key. To do this, you can first create the column. Then, somewhere
before the closing parenthesis of the table, specify the primary key column using the following formula:
CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)
In this formula, the CONSTRAINT keyword
and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName
placeholder, enter the name you want to give to the primary key. In the
parentheses of the PRIMARY KEY expression, enter the name of the column that
will be used as the primary key. Here is an example:
CREATE TABLE Persons
(
PersonID int identity(1,1) NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID)
);
By convention or tradition, the name of
the primary starts with PK_ followed by the name of the table. Here is an
example:
USE Exercise2;
GO
CREATE TABLE Persons
(
PersonID int identity(1,1) NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO
Continuing with our car rental database, imagine a customer
comes to rent a car. We already established that it would
be redundant to create new car information every time you process a new
customer order. Instead, you would get the car's information
from the table that holds data for the cars, and provide that information to the table used to
process orders. As we described earlier, the car table should be
able to provide its data to the other tables that would need that data. To
make this flow of information possible from one table to another, you must
create a relationship between them.
To make it possible for a table B to receive data
from a table A, the table B must have a column that represents the table
A. This columns acts as an "ambassador" or a link. As a
pseudo-ambassador, the column in the table B almost does not belong to
that table: it primarily allows both tables to communicate. For this
reason, the column in the table B is called a foreign key.
A foreign key is a column on a table whose data is
coming from another table.
|
Creating a Foreign Key in the Table Design View |
|
To
create a foreign key in the Table Design window, in the table that will
receive the key, simply create a column with the following rules:
- The column should have the same name as the primary column of the table it
represents (but this is not a requirement)
- The column must (this is required) have the same data type as the primary column of the table
it represents
Here is an example of a column named GenderID that is a
foreign key:
Obviously in order to have information flowing from one
table to another, the table that holds the primary information must be created.
You can create it before or after creating the other table, as long as you
have not established any link between both tables, it does not matter what
sequence you use to create them.
The table that contains a primary key and that holds the
information that another table would use is called the primary table or the
parent table. The table that will receive the information from the other table
is called the foreign table or the child table.
|
Creating a Foreign Key in the Relationships Dialog Box |
|
To create a foreign key in a table:
- From the Object Explorer or the Server Explorer, open the child table in Design View
- Right-click anywhere in the table and click Relationships...
- In the Foreign Key Relationships dialog box, click Add
- A default name would be suggested to you. You can accept or change it. To
change the name of the foreign key, on the right side, expand Identity and
edit the string in the (Name) field:
- If necessary, in the same way, you can create other foreign keys by
clicking Add. To delete an existing foreign key, first select it under
Selected Relationships and click Delete.
Once you are ready, click Close
|
Creating a Foreign Key in SQL |
|
You can also create a foreign key in the SQL. The basic
formula to use is: FOREIGN KEY REFERENCES ParentTableName(ForeignKeyCcolumn)
The FOREIGN KEY expression and the REFERENCES
keyword are required. In the ParentTableName placeholder, enter the name
of the primary table that holds the information that will be accessed in the
current table. In the parentheses of ParentTableName, enter the name of
the primary column of the parent table. Here is an example: CREATE TABLE Persons
(
PersonID int identity(1,1) PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);
|
The Foreign Key Constraint |
|
Notice that the foreign key does not have an object name as
we saw for the primary key. If you do not specify a name for the foreign key, the
SQL interpreter would automatically create a default name for you. Otherwise, to
create a name, after creating the column, enter the CONSTRAINT keyword followed
by the desired name and continue the rest as we saw above. Her is an example: CREATE TABLE Persons
(
PersonID int identity(1,1) PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
GenderID int NULL CONSTRAINT FKGenders
FOREIGN KEY REFERENCES Genders(GenderID)
);
|
Establishing a Relationship |
|
As mentioned already, a relational database is one in which
information flows from one table to another. To prepare the tables for this, you
create primary and foreign keys, which we have done so far. Once the tables are
ready, you can link them, which is referred to as creating a relationship
between two tables. If you did not create a foreign key with SQL code, you can
create it when establishing a relationship between two tables.
To create a relationship between two tables
- Open the child table in the design view
- Right-click (anywhere in) the table and click Relationships...
If the (necessary) foreign key does not exist, click Add and specify its
name under Identity) on the right side
- Under Selected Relationships, click the foreign key that will hold the
relationship
- On the right side, expand Tables And Columns Specification
- Click its ellipsis button
- In the Primary Key Table combo box, select the parent table that
holds the primary data
- Under the parent table, click and select its primary key column
- Under Foreign Key Table, make sure the name of the current table is
set.
Under the name of the child table, click and select the name of the
foreign key column. Here is an example:
- Click OK.
When a relationship has been created, it would show in the Tables And
Column Specification section:
- In the same way, you can create other relationships by clicking Add
and configuring the link.
Once you have finished, click Close
|
 Practical
Learning: Creating Relationships
|
|
- Start Microsoft Visual Basic and create a new Windows Application named
Exercise5
- In the Solution Explorer, right-click Form1.vb and click Rename
- Type Exercise.vb and press Enter twice
- Double-click the middle of the form
- To create a database, including the tables with their primary keys
and their
foreign keys, implement the Load event as follows:
Imports System.Data.SqlClient
Public Class Exercise
Private Sub Exercise_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
Using conBethesdaCarRental As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Integrated Security='SSPI';")
Dim strCreateDatabase As String = "IF EXISTS ( " & _
"SELECT name " & _
"FROM sys.databases " & _
"WHERE name = N'BethesdaCarRental1' " & _
") " & _
"DROP DATABASE BethesdaCarRental1; " & _
"CREATE DATABASE BethesdaCarRental1"
Dim cmdBethesdaCarRental As SqlCommand = _
New SqlCommand(strCreateDatabase, conBethesdaCarRental)
conBethesdaCarRental.Open()
cmdBethesdaCarRental.ExecuteNonQuery()
MsgBox("A database named N'BethesdaCarRental1 has been created")
End Using
Using conBethesdaCarRental As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='BethesdaCarRental1'; " & _
"Integrated Security='SSPI';")
Dim strCreateTable As String = "CREATE TABLE RentalRates( " & _
"RentalRateID int identity(1, 1) NOT NULL, " & _
"Daily smallmoney, Weekly smallmoney, " & _
"Monthly smallmoney, Weekend smallmoney, " & _
"CONSTRAINT PK_RentalRates PRIMARY KEY (RentalRateID));"
Dim cmdBethesdaCarRental As SqlCommand = _
New SqlCommand(strCreateTable, conBethesdaCarRental)
conBethesdaCarRental.Open()
cmdBethesdaCarRental.ExecuteNonQuery()
MsgBox("A table named RentalRates has been created")
End Using
Using conBethesdaCarRental As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='BethesdaCarRental1'; " & _
"Integrated Security='SSPI';")
Dim strCreateTable As String = "CREATE TABLE Employees( " & _
"EmployeeID int identity(1, 1) NOT NULL, " & _
"EmployeeNumber nchar(5), " & _
"FirstName varchar(32), " & _
"LastName varchar(32) NOT NULL, " & _
"FullName AS (([LastName]+', ')+[FirstName]), " & _
"Title varchar(80), " & _
"HourlySalary smallmoney, " & _
"CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));"
Dim cmdBethesdaCarRental As SqlCommand = _
New SqlCommand(strCreateTable, conBethesdaCarRental)
conBethesdaCarRental.Open()
cmdBethesdaCarRental.ExecuteNonQuery()
MsgBox("A table named Employees has been created")
End Using
Using conBethesdaCarRental As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='BethesdaCarRental1'; " & _
"Integrated Security='SSPI';")
Dim strCreateTable As String = "CREATE TABLE Customers( " & _
"CustomerID int identity(1, 1) NOT NULL, " & _
"DrvLicNumber varchar(50), " & _
"FullName varchar(80), " & _
"Address varchar(100) NOT NULL, " & _
"City varchar(50), " & _
"State varchar(50), " & _
"ZIPCode varchar(20), " & _
"CONSTRAINT PK_Customer PRIMARY KEY (CustomerID));"
Dim cmdBethesdaCarRental As SqlCommand = _
New SqlCommand(strCreateTable, conBethesdaCarRental)
conBethesdaCarRental.Open()
cmdBethesdaCarRental.ExecuteNonQuery()
MsgBox("A table named Customers has been created")
End Using
Using conBethesdaCarRental As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='BethesdaCarRental1'; " & _
"Integrated Security='SSPI';")
Dim strCreateTable As String = "CREATE TABLE Cars( " & _
"CarID int identity(1, 1) NOT NULL, " & _
"TagNumber varchar(20), " & _
"Make varchar(50), " & _
"Model varchar(50) NOT NULL, " & _
"CarYear smallint, " & _
"Category varchar(50), " & _
"PictureLocation varchar(200), " & _
"CDPlayer bit, " & _
"DVDPlayer bit, " & _
"Available bit, " & _
"CONSTRAINT PK_Car PRIMARY KEY (CarID));"
Dim cmdBethesdaCarRental As SqlCommand = _
New SqlCommand(strCreateTable, conBethesdaCarRental)
conBethesdaCarRental.Open()
cmdBethesdaCarRental.ExecuteNonQuery()
MsgBox("A table named Cars has been created")
End Using
Using conBethesdaCarRental As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='BethesdaCarRental1'; " & _
"Integrated Security='SSPI';")
Dim strCreateTable As String = "CREATE TABLE RentalOrders( " & _
"RentalOrderID int identity(1, 1) NOT NULL, " & _
"DateProcessed datetime, " & _
"EmployeeID int Constraint " & _
"FK_Employees References " & _
"Employees(EmployeeID) NOT NULL, " & _
"EmployeeName varchar(80), " & _
"CustomerID int Constraint " & _
"FK_Customers References " & _
"Customers(CustomerID) NOT NULL, " & _
"CustomerName varchar(80), " & _
"CustomerAddress varchar(100), " & _
"CustomerCity varchar(50), " & _
"CustomerState varchar(50), " & _
"CustomerZIPCode varchar(20), " & _
"CarID int Constraint " & _
"FK_Cars References Cars(CarID) NOT NULL, " & _
"CarMake varchar(50), " & _
"CarModel varchar(50), " & _
"CarYear smallint, " & _
"CarCondition varchar(50), " & _
"TankLevel varchar(40), " & _
"MileageStart int, " & _
"MileageEnd int, " & _
"RentStartDate datetime, " & _
"RendependDate datetime, " & _
"Days int, " & _
"RateApplied money, " & _
"SubTotal money, " & _
"TaxRate decimal(6, 2), " & _
"TaxAmount money, " & _
"OrderTotal money, " & _
"OrderStatus varchar(50), " & _
"CONSTRAINT PK_RentalOrder " & _
"PRIMARY KEY (RentalOrderID));"
Dim cmdBethesdaCarRental As SqlCommand = _
New SqlCommand(strCreateTable, conBethesdaCarRental)
conBethesdaCarRental.Open()
cmdBethesdaCarRental.ExecuteNonQuery()
MsgBox("A table named RentalOrder has been created")
End Using
End Sub
End Class
|
- Execute the application
- Close the form and return to your programming environment
- In the source file, delete the whole content of the Load event
- In the Server Explorer, right-click the Data Connections node and
click Add Connection
- In the Server Name combo box of the Add Connection dialog box, type
(local) or select the name of the server
- In the Select Or Enter A Database Name combo box, select
BethesdaCarRental1 and click Test Connection
- Click OK twice
A diagram is a window that visually displays the
relationships among the tables of a database. To create a diagram:
- In the Object Explorer in Microsoft SQL Server Management Studio or
in the Server Explorer in Microsoft Visual Studio, in the database node, you can click Database
Diagrams
- A dialog box will inform you that this database does not have a
diagram. Read the message and click Yes
- Right-click Database Diagrams and click New Database Diagram
- In the Add Table dialog box, click each table and click the Add.
Alternatively, you can double-click a table to add it
- In the Add Table dialog box, you can click
Close.
On the toolbar, you can click the Zoom button and select a larger or
smaller value.
To move a table, you can drag its title bar. Here is an example:
- To establish a relationship, you can click the gray box on the left of
any column from the parent table and drop it on any column in the
child table. A better way is to click gray box of the primary key
column from the parent table, drag that box then drop it on the
foreign key column of the child table. Here is an example:
- A Tables and Columns dialog box would come up. It would display the
column that was dragged and the column on which you dropped.
If you had selected just any column, it would show but it may not be
the one you wanted to drag; that is, it may not be the actual column
that is supposed to manage the relationship.
Regardless, under Primary Key Table, you should select the parent
table
- Under the parent table, select its primary column
- Under Foreign Table, select the foreign key column. Here is an
example:
- Once you are ready, click OK. A link would be created between the
tables
- In the same way, you can create other relationships.
When you have finished, you can save and close the database
|
Practical
Learning: Creating a Diagram
|
|
- In the Server Explorer, expand the BethesdaCarRental1 node and click
the + button of the Database
Diagrams node
- A dialog box will inform you that this database does not have a
diagram.
Read the message and click Yes
- Right-click Database Diagrams and click Add New Diagram
- In the Add Table dialog box, click Employees and click Add
- Double-click Cars to add it
- In the same way, add the Customers and the RentalOrders tables
- On the Add Table dialog box, click Close.
Notice that, based on how we created the database and its objects, the
relationships have been created already
- To save the diagram, on the Standard toolbar, click Save
- Set its name to dgmBethesdaCarRental and click OK
- Close the window
On a typical database, information comes and goes. For a
car rental company, car information is created and deleted on a regular basis.
When information about a car is deleted, there is concern about the rental
orders related to
that car. Referential integrity allows you to manage these aspects of a
database. You need to make sure that when data is deleted from a parent table, the
child tables are notified and their related records are deleted also. When
information is changed on a parent table, the related information is changed in
the child tables.
To manage referential integrity, you use the Foreign Key
Relationships dialog box. You can access it from the design view of a table or
from the diagram window.
|
Practical
Learning: Managing Referential Integrity
|
|
- In the Server Explorer, expand the Tables node under
BethesdaCarRental1.
Right-click RentalOrders and click Open Table Definition
- Right-click in the table and click Relationships
- Under Selected Relationships, click FK_Customers. In the right
section, expand INSERT And UPDATE Specification
- Click Delete Rule. In its combo box, select Cascade
- Click Update Rule. In its combo box, select Cascade:
- In the same way, specify the following
| Foreign Key |
Delete Rule |
Update Rule |
| FK_Cars |
Cascade |
Cascade |
| FK_Customers |
Cascade |
Cascade |
| FK_Employees |
Cascade |
Cascade |
- Click Close
- Save and close the table
|
|
