Home

Relationships and Data Integrity

 

The Primary Key

 

Relational Databases

A relational database is a system in which information flows from one database object to another. For example, on a bank database, you can use one object to create accounts for customers and use another object to process transactions that the owners of bank accounts need. The reason is that the same customer may need to perform various transactions, regularly. Instead of creating a new account every time the customer wants to perform a new transaction, you can use one account as a reference and bring up this account whenever the customer wants to deposit or withdraw money.

To apply the rules of relational databases, you create some types of relationships among the objects of the database.

The transactions among the various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

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:

  1. 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.
  2. A table (A) that holds information should make that information available to other tables (such as B)
  3. 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 Account table of a bank database can be set on a Bank Account field because each customer should have a unique bank account 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.

Practical LearningPractical Learning: Introducing Relationships

  1. Open SQL Server Management Studio and connect to the server
  2. Right-click the name of the server and click New Query
  3. To start a new database, type the following code:
     
    -- =============================================
    -- Database:     YugoNationalBank
    -- Author:       FunctionX
    -- Date Created: Monday 09 April 2007
    -- =============================================
    USE master
    GO
    
    -- Drop the database if it already exists
    IF  EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'YugoNationalBank'
    )
    DROP DATABASE YugoNationalBank
    GO
    
    CREATE DATABASE YugoNationalBank
    GO

Visually Creating a Primary Key

To create a primary key in SQL Server Management Studio, create a column and specify its data type:

  • Then, on the toolbar, click the Set Primary Key button Primary Key
  • 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

The Foreign Key

 

Introduction

Continuing with our bank database, imagine a customer comes to the bank to deposit money. We already established that it would be redundant to create a new account every time the customer comes to perform a transaction. Instead, you would get the customer's information from his or her account, provide that information to the table used to process transactions. As we described earlier, the account 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.

Creating a Foreign Key in the Table Design View

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 doesn't 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.

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 haven't established any link between both tables, it doesn't 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:

  1. From the Object Explorer, open the child table in Design View
  2. Right-click anywhere in the table and click Relationships...
     
  3. In the Foreign Key Relationships dialog box, click Add
  4. A default name would be suggested to you. You can accept or change it. To change the name of the foreign key, in the right side, expand Identity and edit the string in the (Name) field:
     
  5. 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 doesn't have an object name as we saw for the primary key. If you don't 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

 

Introduction

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 didn't create a foreign key with SQL code, you can create it when establishing a relationship between two tables.

Creating a Relationship

To create a relationship between two tables

  1. Open the child table in the design view
  2. Right-click (anywhere in) the table and click Relationships...
    If the (necessary) foreign key doesn't exist, click Add and specify its name under Identity) in the right side.
  3. Under Selected Relationships, click the foreign key that will hold the relationship
  4. In the right side, expand Tables And Columns Specification
  5. Click its ellipsis button
  6. In the Primary Key Table combo box, select the parent table that holds the primary data
  7. Under the parent table, click and select its primary key column
  8. 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:
     
  9. Click OK.
    When a relationship has been created, it would show in the Tables And Column Specification section:
     
  10. In the same way, you can create other relationships by clicking Add and configuring the link.
    Once you have finished, click Close

Practical LearningPractical Learning: Creating Relationships

  1. To complete our database with its tables, their primary keys, their foreign keys, and some data in the tables, change the code in the query window as follows:
     
    -- =============================================
    -- Database:     YugoNationalBank
    -- Author:       FunctionX
    -- Date Created: Monday 09 April 2007
    -- =============================================
    USE master
    GO
    
    -- Drop the database if it already exists
    IF  EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'YugoNationalBank'
    )
    DROP DATABASE YugoNationalBank
    GO
    
    CREATE DATABASE YugoNationalBank
    GO
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    Locations
    -- =========================================
    USE YugoNationalBank
    GO
    
    IF OBJECT_ID('dbo.Locations', 'U') IS NOT NULL
      DROP TABLE dbo.Locations
    GO
    
    CREATE TABLE Locations
    (
        LocationID int Identity(1,1) NOT NULL, 
        LocationCode varchar(10) NOT NULL,
        Address varchar(120),
        City varchar(50),
        State varchar(50),
        Notes text NULL, 
        CONSTRAINT PK_Locations PRIMARY KEY (LocationID)
    );
    GO
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    AccountTypes
    -- =========================================
    USE YugoNationalBank
    GO
    
    IF OBJECT_ID('dbo.AccountTypes', 'U') IS NOT NULL
      DROP TABLE dbo.AccountTypes
    GO
    
    CREATE TABLE AccountTypes
    (
        AccountTypeID int Identity(1,1) NOT NULL, 
        AccountType varchar(40) NOT NULL, 
        Notes text NULL, 
        CONSTRAINT PK_AccountTypes PRIMARY KEY (AccountTypeID)
    );
    GO
    
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    Employees
    -- =========================================
    IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
      DROP TABLE dbo.Employees
    GO
    CREATE TABLE Employees
    (
        EmployeeID int identity(1,1) NOT NULL, 
        EmployeeNumber char(6),
        FirstName varchar(32),
        LastName varchar(32) NOT NULL,
        Title varchar(50),
        CanCreateNewAccount bit,
        HourlySalary smallmoney,
        EmailAddress varchar(100),
        Username varchar(20),
        Password varchar(20),
        Notes text,
        CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
    );
    GO
    
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    Customers
    -- =========================================
    IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
      DROP TABLE dbo.Customers
    GO
    CREATE TABLE Customers
    (
        CustomerID int Identity(1,1) NOT NULL,
        DateCreated datetime,
        AccountNumber varchar(20),
        AccountTypeID int Constraint FK_TypeOfAccount 
    		References AccountTypes(AccountTypeID),
        CustomerName varchar(50) NOT NULL,
        DateUpdated smallDateTime,
        Notes text, 
        CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
    );
    GO
    
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    ChargeReasons
    -- =========================================
    IF OBJECT_ID('dbo.ChargeReasons', 'U') IS NOT NULL
      DROP TABLE dbo.ChargeReasons
    GO
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    Deposits
    -- =========================================
    IF OBJECT_ID('dbo.Deposits', 'U') IS NOT NULL
      DROP TABLE dbo.Deposits
    GO
    
    CREATE TABLE Deposits
    (
        DepositID int identity(1, 1) NOT NULL,
        LocationID int Constraint FK_DepositLocation
    		References Locations(LocationID) NOT NULL,
        EmployeeID int Constraint FK_Clerk 
    		References Employees(EmployeeID),
        CustomerID int Constraint FK_Depositor 
    		References Customers(CustomerID) NOT NULL,
        DepositDate smalldatetime NOT NULL,
        DepositAmount smallmoney NOT NULL,
        Notes text, 
        CONSTRAINT PK_Deposits PRIMARY KEY (DepositID)
    );
    GO
    
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    Withdrawals
    -- =========================================
    IF OBJECT_ID('dbo.Withdrawals', 'U') IS NOT NULL
      DROP TABLE dbo.Withdrawals
    GO
    
    CREATE TABLE Withdrawals
    (
        WithdrawalID int identity(1, 1) NOT NULL,
        LocationID int Constraint FK_WithdrawlLocation 
    		References Locations(LocationID) NOT NULL,
        EmployeeID int Constraint FK_ProcessedBy 
    		References Employees(EmployeeID),
        CustomerID int Constraint FK_CustomerAccount 
    		References Customers(CustomerID) NOT NULL,
        WithdrawalDate smalldatetime NOT NULL,
        WithdrawalAmount smallmoney NOT NULL,
        WithdrawalSuccessful bit NOT NULL,
        Notes text, 
        CONSTRAINT PK_Withdrawas PRIMARY KEY (WithdrawalID)
    );
    
    -- =========================================
    -- Database: YugoNationalBank
    -- Table:    CheckCashing
    -- =========================================
    IF OBJECT_ID('dbo.CheckCashing', 'U') IS NOT NULL
      DROP TABLE dbo.CheckCashing
    GO
    
    CREATE TABLE CheckCashing
    (
        CheckCashingID int identity(1, 1) NOT NULL,
        LocationID int Constraint FK_BranchLocations 
    		References Locations(LocationID) NOT NULL,
        EmployeeID int Constraint FK_Employees 
    		References Employees(EmployeeID),
        CustomerID int Constraint FK_Customers 
    		References Customers(CustomerID) NOT NULL,
        CheckCashingDate smalldatetime NOT NULL,
        CheckCashingAmount smallmoney NOT NULL,
        CheckCashingSuccessful bit NOT NULL,
        Notes text, 
        CONSTRAINT PK_CheckCashing PRIMARY KEY(CheckCashingID)
    );
    GO
  2. Press F5 to execute the statement

Diagrams

A diagram is a window that visually displays the relationships among tables of a database. To create a diagram:

  1. In the Object Explorer, in the database node, you can click Database Diagrams
  2. A dialog box will inform you that this database doesn't have a diagram. Read the message and click Yes
  3. Right-click Database Diagrams and click New Database Diagram
  4. In the Add Table dialog box, click each table and click the Add.
    Alternatively, you can double-click a table to add it
  5. 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:
     
  6. 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:
     
  7. 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
  8. Under the parent table, select its primary column
  9. Under Foreign Table, select the foreign key column. Here is an example:
     
  10. Once you are ready, click OK. A link would be created between the tables
     
  11. In the same way, you can create other relationships.
    When you have finished, you can save and close the database

Practical LearningPractical Learning: Creating a Diagram

  1. In the Object Explorer, in the YugoNationalBank node, click Database Diagrams
  2. A dialog box will inform you that this database doesn't have a diagram:
     


    Read the message and click Yes
  3. Right-click Database Diagrams and click New Database Diagram
  4. In the Add Table dialog box, click Customers and click the Add > button
  5. Double-click CheckCashing to add it
  6. In the same way, add the AccountTypes , Deposits, Employees, Locations, and Withdrawals tables
  7. 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:
     
    Database Diagram
  8. To save the diagram, on the Standard toolbar, click Save
  9. Set its name to dgmYugoNationalBank and click OK
  10. Close the window

Referential Integrity

On a typical database, information comes and goes. For a bank, customers accounts are created and deleted on a regular basis. When an account is deleted, there is concern about the transactions related to that account. 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 LearningPractical Learning: Managing Referential Integrity

  1. In the Object Explorer, expand the Tables node under YugoNationalBank.
    Right-click dbo.Customers and click Design
  2. Right-click in the table and click Relationships
  3. Under Selected Relationships, click FK_CustomerAccount. In the right section, expand INSERT And UPDATE Specification
  4. Click Delete Rule. In its combo box, select Cascade
  5. Click Update Rule. In its combo box, select Cascade:
     
  6. In the same way, specify the following
     
    Foreign Key Delete Rule Update Rule
    FK_ChargeReasons Cascade Cascade
    FK_Customers Cascade Cascade
    FK_Depositor Cascade Cascade
    FK_TypeOfAccount Cascade Cascade
  7. Click Close
  8. Save and close the table
  9. In the same way, open the Deposits table in design view
  10. Access its Relationships dialog box
  11. Access the properties of its FK_Clerk field.
    Specify its Delete Rule and its Update Rule both to Cascade
  12. Perform the same actions for the other relationships
  13. Close the Relationships dialog box
  14. Save and close the table
 

Previous Copyright © 2007-2012 FunctionX Next