Home

Databases Constraints
 

The Primary Key Constraint
 

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 or a group of columns as the "key" of the table. Such a column or group of columns is called 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 a Customers table of a bank database can be set on a Bank Account field because each customer should have a unique bank account number.

Once you have decided that a table will have a primary key, you must decide what type of data the field(s) 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 nchar) or varchar (or nvarchar) 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. In most cases when you hesitate, the data type of the primary key should be an integer, usually int.

Among the rules you must follow, a table can have only one primary key. If you use one column as the primary key, that column cannot hold a NULL value.

Practical LearningPractical Learning: Introducing Relationships
  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. Right-click the name of the server and click New Query
  4. To start a new database, type the following code:
    -- =============================================
-- Database:     KoloBank1
-- 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'KoloBank1'
)
DROP DATABASE KoloBank1
GO

CREATE DATABASE KoloBank1
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Microsoft SQL Server Database Development\KoloBank1.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Microsoft SQL Server Database Development\KoloBank1.ldf');
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:

Table

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 nvarchar(20),
    LastName nvarchar(20) NOT NULL
);

A Constraint on the Primary Key

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 nvarchar(20),
    LastName nvarchar(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:

CREATE TABLE Persons
(
    PersonID int identity(1,1) NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO

The Foreign Key Constraint
 

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.

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. Unlike a primary key that must be unique, a table can have as many foreign keys as possible since each foreign key would relate to a different table. Still, Microsoft recommends a maximum of 253 foreign keys per 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:

Table

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...
     
    Foreign Key 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:
     
    Foreign Key Relationships
  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 nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);

A Constraint on a Foreign Key

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 nvarchar(20),
    LastName nvarchar(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 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:
     
    Tables and Columns
  9. Click OK.
    When a relationship has been created, it would show in the Tables And Column Specification section:
     
    Foreign Key Relationships
  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:     KoloBank1
-- 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'KoloBank1'
)
DROP DATABASE KoloBank1
GO

CREATE DATABASE KoloBank1
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Microsoft SQL Server Database Development\KoloBank1.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Microsoft SQL Server Database Development\KoloBank1.ldf');
GO
-- =========================================
-- Database: KoloBank1
-- Table:    Locations
-- =========================================
USE KoloBank1
GO

IF OBJECT_ID(N'dbo.Locations', N'U') IS NOT NULL
  DROP TABLE dbo.Locations
GO

CREATE TABLE Locations
(
    LocationID int Identity(1,1) NOT NULL, 
    LocationCode nvarchar(10) NOT NULL,
    Address nvarchar(120),
    City nvarchar(50),
    State nvarchar(50),
    Notes nvarchar(max) NULL, 
    CONSTRAINT PK_Locations PRIMARY KEY (LocationID)
);
GO
-- =========================================
-- Database: KoloBank1
-- Table:    AccountTypes
-- =========================================
USE KoloBank1
GO

IF OBJECT_ID(N'dbo.AccountTypes', N'U') IS NOT NULL
  DROP TABLE dbo.AccountTypes
GO

CREATE TABLE AccountTypes
(
    AccountTypeID int Identity(1,1) NOT NULL, 
    AccountType nvarchar(40) NOT NULL, 
    Notes nvarchar(max) NULL, 
    CONSTRAINT PK_AccountTypes PRIMARY KEY (AccountTypeID)
);
GO

-- =========================================
-- Database: KoloBank1
-- Table:    Employees
-- =========================================
IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL
  DROP TABLE dbo.Employees
GO
CREATE TABLE Employees
(
    EmployeeID int identity(1,1) NOT NULL, 
    EmployeeNumber char(6),
    FirstName nvarchar(32),
    LastName nvarchar(32) NOT NULL,
    Title nvarchar(50),
    CanCreateNewAccount bit,
    HourlySalary smallmoney,
    EmailAddress nvarchar(100),
    Username nvarchar(20),
    Password nvarchar(20),
    Notes nvarchar(max),
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
GO

-- =========================================
-- Database: KoloBank1
-- Table:    Customers
-- =========================================
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO
CREATE TABLE Customers
(
    CustomerID int Identity(1,1) NOT NULL,
    DateCreated date,
    AccountNumber nvarchar(20),
    AccountTypeID int Constraint FK_TypeOfAccount 
		References AccountTypes(AccountTypeID),
    CustomerName nvarchar(50) NOT NULL,
    DateUpdated date,
    Notes nvarchar(max), 
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
);
GO

-- =========================================
-- Database: KoloBank1
-- Table:    ChargeReasons
-- =========================================
IF OBJECT_ID(N'dbo.ChargeReasons', N'U') IS NOT NULL
  DROP TABLE dbo.ChargeReasons
GO
-- =========================================
-- Database: KoloBank1
-- Table:    Deposits
-- =========================================
IF OBJECT_ID(N'dbo.Deposits', N'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 date NOT NULL,
    DepositAmount money NOT NULL,
    Notes nvarchar(max), 
    CONSTRAINT PK_Deposits PRIMARY KEY (DepositID)
);
GO

-- =========================================
-- Database: KoloBank1
-- Table:    Withdrawals
-- =========================================
IF OBJECT_ID(N'dbo.Withdrawals', N'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 date NOT NULL,
    WithdrawalAmount money NOT NULL,
    WithdrawalSuccessful bit NOT NULL,
    Notes nvarchar(max), 
    CONSTRAINT PK_Withdrawals PRIMARY KEY (WithdrawalID)
);

-- =========================================
-- Database: KoloBank1
-- Table:    CheckCashing
-- =========================================
IF OBJECT_ID(N'dbo.CheckCashing', N'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 date NOT NULL,
    CheckCashingAmount money NOT NULL,
    CheckCashingSuccessful bit NOT NULL,
    Notes nvarchar(max), 
    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:
     
    Diagram
  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:
     
    Diagram
  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:
     
    Tables and Columns
  10. Once you are ready, click OK. A link would be created between the tables
     
    Diagram
  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 KoloBank1 node, click Database Diagrams
  2. A dialog box will inform you that this database doesn't have a diagram:
     
    Microsoft SQL Server Management Studio

    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 dgmKoloBank1 and click OK
  10. Close the window

Referential Integrity
   

Introduction

Data relationships allow records from one object to be available to other objects. When a relationship has been established between two tables, one of the concerns is to plan what would happen if a record from a parent table is deleted or moved.

Referential integrity is the ability to take appropriate actions when tables or records involved in a relationship are affeected. To assist you with this aspect of database management, Both Microsoft SQL Server and Transact-SQL provide various tools.

To visually manage referential integrity, you can use the Foreign Key Relationships dialog box. To access it, first open the child table in the design view. Then:

  • Right-click anywhere in the table and click Relationships...
  • On the main menu, click Table Designer -> Relationships...

Any of these actions would display the Foreign Key Relationships dialog box. As reviewed already, if you had not yet created a foreign key on the table that was displaying, the dialog box would appear empty. We have already seen how to create a foreign key using this dialog box.

Enforcing Referential Integrity

When a relationship has been established between two tables, you can ask the database engine to observe some rules between the tables on one hand and among the records on the other hand. Of course, before setting these rules, you must have created the relationship. Here is an example we saw already:

Tables and Columns

Once this is done, in the Foreign Key Relationships dialog box, expand INSERT And UPDATE Specification. Two combo boxes would appear: Delete Rule and Update Rule

These two combo boxes have the same four options:

Foreign Key Relationships

The opttions can be set either visually or programmatically. Remember how to create a foreign key with code. Here is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FK_Genders FOREIGN KEY REFERENCES Genders(GenderID)
);
GO

To specify what action to take on the foreign key when a record is deleted, add an ON DELETE expression:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
    ON DELETE . . .
);
GO

To specify what action to take on the foreign key when a record has changed, add an ON UPDATE expression:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
    ON UPDATE . . .
);
GO

In both cases, you must specify what option to apply.

An Error On Delete or On Update

The default option is No Cation and it is the first one selected in the Foreign Key Relationships dialog box. Here is an example of setting it with code: 

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
	ON DELETE NO ACTION
);
GO

You would follow the same approach for the update. The No Action option asks the database engine to issue an error if the record in the parent is deleted or updated while at least one record of the child table uses that parent record. Consider the following tables:

CREATE TABLE Genders
(
    GenderID int not null,
    Gender nvarchar(20),
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO
INSERT INTO Genders
VALUES(1, N'Male'), (2, N'Female'), (3, N'Unknown');
GO

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
	ON DELETE NO ACTION
);
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'James', N'Palau', 1),
      (N'Ann', N'Nsang', 2),
      (N'Marc', N'Ulrich', 1),
      (N'Arjuh', N'Namdy', 3),
      (N'Aisha', N'Diabate', 2);
GO

Here is an example of showing all records of the table:

Cascading On Delete or On Update 

Now, if you try to delete one of the records of the Genders table, you would receive an error. Here is an example:

An Error On Delete or On Update

In the same way, if you had set the update to No Action, if you try updating a parent record and if the change would impact a child record, the database engine would throw an error. Consider the following code:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
);
GO

Here is an attempt to update a parent record:

An Error On Delete or On Update

Cascading On Delete or On Update

The Cascade option indicates that, if something happens to a record in the parent table, the child records receive the change. For example, if you are using the Delete Rule, if a record is deleted in the parent table and if some records in the child table use the value in the parent table, those records in the child table get deleted.

To visually apply the cascade option, if you are working visually, in the Foreign Key Relationships dialog box, click the combo box of either Delete Rule or Update Rule and select Cascade. To set it programmatically, add CASCADE after ON DELETE or ON UPDATE. Here is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
	ON DELETE CASCADE
);
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'James', N'Palau', 1),
      (N'Ann', N'Nsang', 2),
      (N'Marc', N'Ulrich', 1),
      (N'Arjuh', N'Namdy', 3),
      (N'Aisha', N'Diabate', 2);
GO

Here is an example of deleting a record from a parent table:

Cascading On Delete or On Update

Notice that the records in the child table that were getting their values from the parent table have also been deleted.

If you apply the cascade option to the Update Rule, when a record of the parent table is changed, the child records receive the change.

Setting NULL On Delete or On Update

Instead of displaying a nasty error or even deleting records on cascade when something happens to a record of a parent table, probably a better option is to reset to NULL every record of the child table if that record is related to the parent table. To do this visually, in the Delete Rule or the Update Rule, select Set Null. To do this programmatically, after ON DELETE or ON UPDATE, add SET NULL. Here is an example:

DROP TABLE Persons;
GO
DROP TABLE Genders;
GO
CREATE TABLE Genders
(
    GenderID int not null,
    Gender nvarchar(20),
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO
INSERT INTO Genders
VALUES(1, N'Male'), (2, N'Female'), (3, N'Unknown');
GO
CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
	ON DELETE SET NULL
);
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'James', N'Palau', 1),
      (N'Ann', N'Nsang', 2),
      (N'Marc', N'Ulrich', 1),
      (N'Arjuh', N'Namdy', 3),
      (N'Aisha', N'Diabate', 2);
GO

Here is an example of showing all records of the table:

Cascading On Delete or On Update 

Here is an example of deleting a record from a parent table:

Cascading On Delete or On Update

The update follows the same logic: If a record of the parent table is updated, any record in the child table and that gets its value from the parent table would have its value set to NULL.

Applying the Default Value On Delete or On Update

If a column of a parent table has a default value, when a record of that column is affected by some action, you can ask the database engine to apply the default value to the related records of the child table. To do this programmatically, use ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT. Here is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int default 3
    CONSTRAINT FK_Genders FOREIGN KEY REFERENCES Genders(GenderID)
	ON DELETE SET DEFAULT
);
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'James', N'Palau', 1),
      (N'Ann', N'Nsang', 2),
      (N'Marc', N'Ulrich', 1),
      (N'Arjuh', N'Namdy', NULL),
      (N'Aisha', N'Diabate', 2);
GO

Here is an example of showing the records of the table:

Here is an example of showing all records of the table:

Here is an example of delete a record from the parent table and showing the records of the child table:

Here is an example of showing all records of the table:

Practical LearningPractical Learning: Managing Referential Integrity
  1. In the Object Explorer, expand the Tables node under KoloBank1.
    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

Constraints Maintenance
 

Introduction

There are many issues you need to be concerned about in a relational database. Some issues are related to the columns of tables (and views) and some issues have to do with records. This means that you should always think about structures of tables when you decide to either add new columns or delete records.

If you decide to delete a table, first check if it involved in a relationship. If so, is it the parent (is it providing its records to another table?) or the child (is one of its columns receiving values from a parent table?)? If a table is a child, you can easily delete it using any of the techniques we know already. If a table is a parent, you will receive an error. Consider the following two tables

CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null PRIMARY KEY,
    Gender nvarchar(20)
);
GO
CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY REFERENCES Genders(GenderID)
);
GO

INSERT INTO Genders(Gender)
VALUES(N'Make'), (N'Female'), (N'Unknown');
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Peter', N'Mukoko', 1), (N'Ann', N'Nsang', 2);
GO

If you try to delete the Genders table, which is a parent to the Persons, table, you would receive an error:

If you try to delete the Genders table, which is a parent to the Persons, table, you would receive an error

To avoid this problem (this error), you can first delete the child table.

Adding a Primary Key

After creating a table or when inheriting a table created by someone else, you may find out that it lacks a primary key. You can add it, of course following some rules. You have two options.

Imagine you have the following table:

CREATE TABLE Employees
(
    FirstName nvarchar(20),
    LastName nvarchar(20),
    DepartmentCode nchar(6)
);
GO

You can add the PRIMARY KEY expresion after defining the new column. Here is an example:

ALTER TABLE Employees
    ADD EmployeeNumber int not null PRIMARY KEY;
GO

As an alternative, you can add a column, and then use the CONSTRAINT formula to define the primary key. Here is an example:

ALTER TABLE Employees
    ADD EmployeeNumber int not null
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber);
GO

Adding a Foreign Key

Just as you add a primary key to an already created table, you can also add a new column that is a foreign key. Consider the following table named Persons:

CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null PRIMARY KEY,
    Gender nvarchar(20)
);
GO
CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);
GO

The formula to add a foreign key to an existing table is:

ALTER TABLE TableName
ADD NewColumnName DataType Options
    FOREIGN KEY REFERENCES ParentTableName(ColumnNameOfOtherTable);
GO

Here is an example of adding a foreign key to the above Persons table:

ALTER TABLE Persons
ADD GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID);
GO

Check Constraints
 

Introduction

When performing data entry, in some columns, even after indicating the types of values you expect the user to provide for a certain column, you may want to restrict a range of values that are allowed. In the same way, you can create a rule that must be respected on a combination of columns before the record can be created. For example, you can ask the database engine to check that at least one of two columns received a value. For example, on a table that holds information about customers, you can ask the database engine to check that, for each record, either the phone number or the email address of the customer is entered.

The ability to verify that one or more rules are respected on a table is called a check constraint. A check constraint is a Boolean operation performed by the SQL interpreter. The interpreter examines a value that has just been provided for a column. If the value is appropriate:

  1. The constraint produces TRUE
  2. The value gets accepted
  3. The value is assigned to the column

If the value is not appropriate:

  1. The constraint produces FALSE
  2. The value gets rejected
  3. The value is not assigned to the column

You create a check constraint at the time you are creating a table.

Visually Creating a Check Constraint

To create a check constraint, when creating a table, right-click anywhere in (even outside) the table and click Check Constraints...

Check Constraints

This would open the Check Constraints dialog box. From that window, you can click Add. Because a constraint is an object, you must provide a name for it. The most important piece of information that a check constraint should hold is the mechanism it would use to check its values. This is provided as an expression. Therefore, to create a constraint, you can click Expression and click its ellipsis button. This would open the Check Constraint Expression dialog box.

To create the expression, first type the name of the column on which the constraint will apply, followed by parentheses. In the parentheses, use the arithmetic and/or SQL operators we studied already. Here is an example that will check that a new value specified for the Student Number is greater than 1000:

Check Constraint Expression

After creating the expression, you can click OK. If the expression is invalid, you would receive an error and given the opportunity to correct it.

You can create as many check constraints as you judge necessary for your table:

Check Constraints

After creating the check constraints, you can click OK.

Programmatically Creating a Check Constraint

To create a check constraint in SQL, first create the column on which the constraint will apply. Before the closing parenthesis of the table definition, use the following formula:

CONSTRAINT name CHECK (expression) 

The CONSTRAINT and the CHECK keywords are required. As an object, make sure you provide a name for it. Inside the parentheses that follow the CHECK operator, enter the expression that will be applied. Here is an example that will make sure that the hourly salary specified for an employee is greater than 12.50:

CREATE TABLE Employees
(
	[Employee Number] nchar(7),
	[Full Name] varchar(80),
	[Hourly Salary] smallmoney,
	CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50)
);

It is important to understand that a check constraint it neither an expression nor a function. A check constraint contains an expression and may contain a function as part of its definition.

After creating the constraint(s) for a table, in the Object Explorer of Microsoft SQL Server Management Studio, inside the table's node, there is a node named Constraints and, if you expand it, you would see the name of the constraint.

With the constraint(s) in place, during data entry, if the user (or your code) provides an invalid value, an error would display. Here is an example:

An Error From an Invalid Value of Check Constraint

Instead of an expression that uses only the regular operators, you can use a function to assist in the checking process. You can create and use your own function or you can use one of the built-in Transact-SQL functions.

Practical LearningPractical Learning: Creating a Check Constraint
  1. In the Object Explorer, right-click the name of the server and click Start PowerShell
  2. In the PowerShell window, type SQLCMD and press Enter
  3. To create a database and a new table that has a check mechanism, type the following:
    CREATE DATABASE GreensvilleElectricCompany;
GO

CREATE TABLE Customers
(
    AccountNumber nchar(14) primary key not null,
    CustomerName nvarchar(50) NOT NULL,
    PhoneNumber nchar(16),
    EmailAddress nvarchar(50),
    CONSTRAINT CK_CustomerContact
	CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL))
);
GO
     
  4. To add records to the new table, type the following:
    INSERT INTO Customers
VALUES(N'188395805',N'Ann Zeke',N'301-128-3506',N'azeke@yahoo.jp'),
      (N'806200422',N'Joan Simms',N'410-114-6820',N'jsimmson@emailct.com'),
      (N'402628475',N'Peter Dorka',N'(202) 050-1629',N'pdorka@hotmail.com'),
      (N'666204275',N'James Bengbiss',N'443-158-1833',N'jbango@emailct.com');
GO
  5. To try adding a new record to the table, type the following and press Enter after each line:
    1> INSERT INTO Customers
2> VALUES(N'402628475',N'James Darfield',N'202-188-8742',N'');
3> GO
  6. Notice that you receive an error:
    Msg 2627, Level 14, State 1, Server CENTRAL, Line 1
Violation of PRIMARY KEY constraint 'PK__Customer__BE2ACD6E51BA1E3A'. Cannot ins
ert duplicate key in object 'dbo.Customers'.
The statement has been terminated.
1>
  7. To delete the database, type:
    DROP DATABASE GreensvilleElectricCompany;
GO
  8. Type exit and press Enter
  9. To close the PowerShell window, type exit and press Enter

Exercises
   

Lesson Summary Questions
  1. Which one of the following is a valid way to create a primary key? 
    1. CREATE TABLE Employees
(
    PRIMARY KEY EmployeeID int identity(1,1),
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);
    2. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) IS PRIMARY KEY,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);
    3. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) PRIMARY KEY,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);
    4. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) MAKE PRIMARY KEY,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);
    5. CREATE TABLE Employees
(
    EmployeeID int identity(1,1),
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    SET EmployeeID = PRIMARY KEY
);
  2. Imagine you have the following table: 
    CREATE TABLE Genders
(
    GenderID int identity(1, 1) primary key,
    Gender nvarchar(32)
);
GO
    From the following examples, what code would create a foreign key? 
    1. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int SET AS FOREIGN KEY FOR GenderID FROM Genders()
);
    2. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int FOREIGN KEY AS Genders(GenderID)
);
    3. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int,
    CONSTRAINT FK_Genders FOREIGN KEY REFERENCES Genders(GenderID)
);
    4. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);
    5. CREATE TABLE Employees
(
    EmployeeID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int CREATE AS FOREIGN KEY FROM Genders(GenderID)
);
  3. Which of the following codes would create a primary key? 
    1. CREATE TABLE Customers
(
    CustomerID int identity(1,1),
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PRIMARY KEY(CustomerID)
);
    2. CREATE TABLE Customers
(
    CustomerID int identity(1,1),
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)
);
    3. CREATE TABLE Customers
(
    CustomerID int identity(1,1) NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT SET CustomerID AS PRIMARY KEY
);
    4. CREATE TABLE Customers
(
    CustomerID int identity(1,1) NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CREATE PRIMARY KEY CONSTRAINT FOR CustomerID
);
    5. CREATE TABLE Customers
(
    CustomerID int identity(1,1) NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PRIMARY KEY CustomerID AS PK_Customers
);
  4. Imagine you have the following table: 
    CREATE TABLE Genders
(
    GenderID int identity(1, 1) primary key,
    Gender nvarchar(32)
);
GO
    What is a valid way to create a foreign key? 
    1. CREATE TABLE Students
(
    StudentID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int NULL CONSTRAINT FK_Students
                       FOREIGN KEY REFERENCES Genders(GenderID)
);
    2. CREATE TABLE Students
(
    StudentID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int SET FK_Students AS CONSTRAINT 
                       FOREIGN KEY REFERENCES Genders(GenderID)
);
    3. CREATE TABLE Students
(
    StudentID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int SET AS FOREIGN KEY FOR Genders(GenderID)
);
    4. CREATE TABLE Students
(
    StudentID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int,
    CREATE FOREIGN KEY SET FK_Students = GenderID FROM Genders
);
    5. CREATE TABLE Students
(
    StudentID int identity(1,1) primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int CONSTRAINT FOREIGN KEY REFERENCES Genders(GenderID)
);
  5. Imagine you have table and you are creating another table that will be related to the first. If a record of a parent table is deleted and if you want to display an error, what option would you add to the foreign key of the child table?
    1. ON DELETE CASCADE ERROR
    2. ON DELETE NO ACTION
    3. ON DELETE SET NULL
    4. ON DELETE SHOW ERROR
    5. ON DELETE SET REFERENCE ERROR
  6. If you are creating a child table that will use some records that get their values from the parent table, if a record of a parent table is updated and if you want to display an error, what option would you add to the foreign key of the child table?
    1. ON UPDATE SHOW ERROR
    2. ON UPDATE CASCADE ERROR
    3. ON UPDATE NO ACTION
    4. ON UPDATE SET NULL
    5. ON UPDATE SET REFERENCE ERROR
  7. Imagine you are creating a child table that will get some values from another table. When a record of a parent table is deleted and you want the records of the child table to be deleted, what option would you add to the foreign key of the child table?
    1. ON DELETE SET DELETE
    2. ON DELETE NO ACTION
    3. ON DELETE SET NULL
    4. ON DELETE CASCADE
    5. ON DELETE NULL
  8. You are creating a table whose records will get some of their values from a parent table. If a record changes in the parent table and you want to change the corresponding records in the child table, what option would you add to the foreign key of the child table?
    1. ON UPDATE SET DELETE
    2. ON UPDATE CASCADE
    3. ON UPDATE NO ACTION
    4. ON UPDATE SET NULL
    5. ON UPDATE NULL
  9. When creating a table (the child), you want its records to get the values of a certain column from another table, the parent. If a record of a parent table is deleted, you would like the records that use its value in the child table to receive a value of NULL. When creating the foreign key in the child table, what option would you add?
    1. ON DELETE CASCADE NULL
    2. ON DELETE NO ACTION
    3. ON DELETE NULL IS TRUE
    4. ON DELETE SET REFERENCE NULL
    5. ON DELETE SET NULL
  10. While creating a table, you would like it to have a column that gets its values from another table. When creating that column, what option can you add to the foreign key so that when the records of the other table are updated, the column would receive a NULL value?
    1. ON UPDATE SET NULL
    2. ON UPDATE CASCADE NULL
    3. ON UPDATE NO ACTION
    4. ON UPDATE NULL IS TRUE
    5. ON UPDATE SET REFERENCE NULL
  11. You create a table and add a column that has a default value. Then you create another table that has a column that will receive values from the first table. When creating the foreign key of that column, what option can you add so that when the records of the first table are deleted, the column would receive a default value?
    1. ON DELETE DEFAULT = NULL
    2. ON DELETE CASCADE DEFAULT
    3. ON DELETE SET DEFAULT
    4. ON DELETE NO ACTION
    5. ON DELETE DEFAULT IS TRUE
  12. You had created a table that has a column with a default value. Now you are creating another table that has a column that must receive its values from the first table. When creating the foreign key of that column, what option can you add so that when the records of the first table are updated, the column would receive a default value?
    1. ON UPDATE SET DEFAULT
    2. ON UPDATE DEFAULT = NULL
    3. ON UPDATE CASCADE DEFAULT
    4. ON UPDATE NO ACTION
    5. ON UPDATE DEFAULT IS TRUE

Answers
  1. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  2. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  3. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  4. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  5. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  6. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  7. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  8. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  9. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  10. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  11. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  12. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
 
 
   
 
Previous Copyright © 2007-2013, FunctionX, Inc. Next