Microsoft SQL Server - Lesson 26: Referential Integrity

	Referential Integrity

Fundamentals of 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 affected. 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 options 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 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 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 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 Caption 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 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 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(PersonID, FirstName, LastName, GenderID)
VALUES(1, N'James', N'Palau', 1),
      (2, N'Ann', N'Nsang', 2),
      (3, N'Marc', N'Ulrich', 1),
      (4, N'Arjuh', N'Namdy', 3),
      (5, 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:

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 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
INSERT INTO Persons(PersonID, FirstName, LastName, GenderID)
VALUES(1, N'James', N'Palau', 1),
      (2, N'Ann', N'Nsang', 2),
      (3, N'Marc', N'Ulrich', 1),
      (4, N'Arjuh', N'Namdy', 3),
      (5, N'Aisha', N'Diabate', 2);
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:

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 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(PersonID, FirstName, LastName, GenderID)
VALUES(1, N'James', N'Palau', 1),
      (2, N'Ann', N'Nsang', 2),
      (3, N'Marc', N'Ulrich', 1),
      (4, N'Arjuh', N'Namdy', 3),
      (5, N'Aisha', N'Diabate', 2);
GO

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

DELETE FROM Genders
WHERE GenderID = 2;
GO
SELECT ALL * FROM Persons;
GO

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 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(PersonID, FirstName, LastName, GenderID)
VALUES(1, N'James', N'Palau', 1),
      (2, N'Ann', N'Nsang', 2),
      (3, N'Marc', N'Ulrich', 1),
      (4, N'Arjuh', N'Namdy', 3),
      (5, N'Aisha', N'Diabate', 2);
GO
SELECT ALL * FROM Persons;
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:

DELETE FROM Genders
WHERE GenderID = 2;
GO

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:

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 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(PersonID, FirstName, LastName, GenderID)
VALUES(1, N'James', N'Palau', 1),
      (2, N'Ann', N'Nsang', 2),
      (3, N'Marc', N'Ulrich', 1),
      (4, N'Arjuh', N'Namdy', NULL),
      (5, 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:

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:

The constraint produces TRUE
The value gets accepted
The value is assigned to the column

If the value is not appropriate:

The constraint produces FALSE
The value gets rejected
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... 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. 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.