SQL Server Home

Relationships and Data Integrity

Introduction to Keys

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.

Practical LearningPractical Learning: Introducing Relationships
  1. Open Enterprise Manager and expand everything
  2. Right-click the Databases node and click New Database
  3. Specify the Name of the database as NationalBank1 and click OK

The Primary Key

The transactions among 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 two issues that must be dealt with:

  1. Each record that a table (A) holds should/must be unique among all the other records of the same table (A). For example, if you create a list of bank accounts for different customers on a table, you should make sure that there is a unique (no duplicate) bank account number for each customer because each customer should have one and must have one account number. This ensures that there are no duplicate records on the table.
  2. A table (A) that holds information must make it 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.

These two 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 in SQL Server, 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 as 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 constraint. Such a field should have an int data type.

To specify a primary key on a table, you create one column as the PRIMARY KEY constraint and there can be only one PRIMARY KEY constraint on a table. To do this in Enterprise Manager, create a column and specify its data type. Then, on the toolbar, click the Set Primary Key button Primary Key.

To create a primary column using SQL, on the right side of the column definition, type PRIMARY KEY.
 

 

Practical LearningPractical Learning: Setting a Primary Key
  1. Right-click the new NationalBank1 database and click New -> Table
  2. Specify the first Column Name as AccountTypeID, set its Data Type to int
  3. To make it the Primary Key constraint, while the field is still selected, on the toolbar, click the Set Primary Key button Primary Key
  4. Notice that the Allows Nulls check mark is removed
    While the field is still selected, in the bottom section of the Design Table, set the Identity field to Yes and accept the Identity Seed and the Identity Increment to 1 each
  5. Create the rest of the table as follows:
     
    Column Name Data Type Length Allow Nulls
    AccountTypeID int 4  
    AccountType varchar 40 Unchecked
    Notes Text    
  6. To save the table, click its System close button
  7. When asked whether you want to save it, click Yes
  8. Specify the name of the table as AccountTypes and click OK
  9. Expand the NationalBank1 node and expand its Tables child node
  10. Right-click Tables and click New Table...
  11. Fill the new table as follows:
     
    Column Name Data Type Length Allow Nulls Properties
    EmployeeID (Primary Key) int 4   Identity: Yes
    Identity Seed: 1
    Identity Increment: 1
    EmployeeNumber char 6    
    FirstName varchar 32    
    LastName varchar 32 Unchecked  
    Title varchar 50    
    CanCreateNewAccount bit      
    EmailAddress varchar 50    
    WorkPhone varchar 20    
    Extension smallint      
    Address  varchar 120 Unchecked  
    City  varchar 40 Unchecked  
    State  varchar 40 Unchecked  
    ZIPCode  varchar 12 Unchecked  
    Country varchar 50   Default: USA
    HomePhone varchar 20 Unchecked  
    Salary smallmoney      
    Notes  text      
  12. Close the table
  13. When asked whether you want to save it, click Yes
  14. Type Employees and press Enter
  15. Open SQL Query Analyzer
  16. To create another table, type the following:
      
    USE NationalBank
GO
Create Table TransactionTypes (
TransactionTypeID int Primary Key IDENTITY(1, 1) NOT NULL, 
TransactionType varchar(50) NOT NULL,
Notes text)
GO
  17. Press F5 to execute
  18. After the table has been created, replace the statement with the following:
      
    Create Table ChargeReasons (
ChargeReasonID int IDENTITY(1, 1) Primary Key NOT NULL,
ChargeReason varchar(50) NOT NULL,
Notes text)
GO
  19. Execute it
  20. Return to Enterprise Manager and refresh the list of the NationalBank tables (right-click the Tables node and click Refresh)

Foreign Keys

A foreign key is a column on a table whose data is coming from another table. As mentioned above for the primary key. Imagine you want to perform transactions for an ice cream shop. When a customer places an order, she must specify the type of container (cup, cone, or bowl which exist in their own table). To make this happen, you can create a table for orders. In that table, you can create a column that would represent containers so the user would not have to type the name of a container. The clerk would simply select one. Therefore, on the table for orders, the column that represents containers is called a foreign key.

To create a foreign key, the table you want to link to must have a PRIMARY KEY constraint. In the current table, you must add a column referred to as a FOREIGN KEY constraint. The new column in this table should have the same name as the primary key column of the other table.

Practical LearningPractical Learning: Creating Foreign Keys
  1. In Enterprise Manager, create a new table with the following columns:
     
    Column Name Data Type Length Allow Nulls Properties
    CustomerID (Primary Key) int     Identity: Yes
    Identity Seed: 1
    Identity Increment: 1
    DateCreated  datetime      
    AccountNumber  varchar 20    
    AccountTypeID  int      
    AccountName  varchar 50 Unchecked  
    Address  varchar 120 Unchecked  
    City varchar 40 Unchecked  
    State  varchar 40 Unchecked  
    ZIPCode  varchar 12 Unchecked  
    Country  varchar 50   Default: USA
    EmailAddress  varchar 50    
    HomePhone  varchar 20 Unchecked  
    WorkPhone varchar 20    
    WorkExtension varchar 20    
    DateUpdated  smallDateTime      
    Notes text      
  2. Save the table as Customers and close it
  3. To create a new table, right-click the Tables node of NationalBank1 and click New Table
  4. Fill it up as follows:
     
    Column Name Data Type Length Allow Nulls Properties
    TransactionID (Primary Key) int     Identity: Yes
    Identity Seed: 1
    Identity Increment: 1
    EmployeeID int   Unchecked  
    CustomerID int   Unchecked  
    TransactionTypeID int   Unchecked  
    TransactionDate DateTime   Unchecked  
    TransactionNumber
    		 int   Unchecked  
    DepositAmount money      
    WithdrawalAmount money      
    ServiceCharge smallmoney      
    ChargeReasonID int      
    Notes text      
  5. Save the table as AccountTransactions and leave it open in Design View

Tables Relationships

To help information flow from one table to another, there must be a relationship between both tables. One table that holds information would supply it to the other table. The table that holds data is considered the parent and must have a primary key column. The table that request the information must have a foreign key that corresponds to the other's primary key.

Practical LearningPractical Learning: Creating a Relationship
  1. Right-click somewhere in the table and click Relationships... 
  2. In the Relationships property page, click the New button
  3. In the Primary Key Table combo box, select Employees
  4. In the first combo box of the grid under Employees, select EmployeeID
  5. In the Foreign Key Table combo box, select AccountTransactions (it should be selected already)
    Click the grid under AccountTransactions to reveal a combo box. In the combo box, select EmployeeID
  6. Click the Cascade Update Related Fields and the Cascade Delete Related Records check boxes
     
    Properties
  7. Click Close
  8. Close the table. When asked to save, click Yes and Yes

Diagrams

A diagram is a window that visually displays the relationships among tables of a database. To create a diagram, use the Create Database Diagram Wizard.

Practical LearningPractical Learning: Creating a Diagram
  1. Right-click the NationalBank1 node, position the mouse on New and click Database Diagram...
  2. In the first page of the Create Database Diagram Wizard, click Next
  3. In the second page, click AccountTransactions and click the Add > button
  4. In the same way, add the AccountTypes, ChargeReasons, Customers, Employees, and TransactionTypes tables
     
    Create Database Diagram Wizard
  5. Click Next and click Finish
  6. On the toolbar, click the Zoom button and select 100%
  7. To move a table, you drag its title bar.
    Position the tables as follows:
     
  8. Click the gray box on the left of the TransactionTypeID field in the TransactionTypes table.
    Click and drag that box then drop it on the TransactionTypeID field of the AccountTransactions table:
     
  9. On the Create Relationship dialog box, make sure TransactionTypeID is selected for the TransactionTypes Primary Key Table and that TransactionTypeID is selected for the AccountTransactions Foreign Key Table
  10. Click the Cascade Update Related Fields and the Cascade Delete Related Fields check boxes
     
  11. Click OK
  12. In the same way, drag the ChargeReasonID row button from ChargeReasons table to the ChargeReasonID field on AccountTransactions table
  13. On the Create Relationship dialog box, make sure ChargeReasonID is selected for the ChargeReasons Primary Key Table and that ChargeReasons is selected for the AccountTransactions Foreign Key Table.
    Click the Cascade Update Related Fields and the Cascade Delete Related Fields check boxes
     
  14. Click OK
  15. Complete the diagram as follows:
     
  16. Close the Diagram window
  17. When asked to save the database diagram, set its name to NatBank and click OK
     
  18. Click Yes to save the database
Previous Copyright © 2004-2012, FunctionX Next