Home

Referential Integrity

   

Referencing a Relationship

   

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:

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:

  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... 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.

Exercises

   

Lesson Summary Questions

  1. What is the basic formula to edit a record of a table?
    1. UPDATE ColumnName AS Expression FROM TableName
    2. UPDATE TableName SET ColumnName Operator Expression
    3. EDIT TableName SET ColumnName Operator Expression
    4. UPDATE ColumnName FROM TableName SET ColumnName = NewValue
    5. EDIT ColumnName FROM TableName SET ColumnName Operator Expression
  2. What is the formula to delete all records from a table?
    1. DROP TableName;
    2. REMOVE [FROM] TableName;
    3. DELETE [FROM] TableName;
    4. EXECUTE sp_removetable TableName;
    5. KILL ALL [FROM] TableName;
  3. What is the formula to change a specific record or specific records of a table?
    1. CHANGE TableName
      SET ColumnName = Expression
      WITH Condition(s)
    2. UPDATE TableName
      WHERE Condition(s)
      SET ColumnName = Expression
    3. EDIT TableName
      SET ColumnName = Expression
      WHERE Condition(s)
    4. UPDATE TableName
      SET ColumnName = Expression
      WHERE Condition(s)
    5. ALTER TableName
      WHERE Condition(s)
      SET ColumnName = Expression
  4. What is the formula to remove a specific record or to remove specific records of a table?
    1. DELETE FROM TableName
      WHERE Condition(s)
    2. DROP TableName
      WHERE Condition(s)
    3. CHANGE ROWS TableName
      WHERE Condition(s)
    4. DELETE FROM TableName
      SET Condition(s) = NULL
    5. ALTER TableName
      SET ColumnName = NULL
  5. If you want to change one or more records from a table and you want to show the result when the operation ends, what formula would you use?
    1. UPDATE TableName
      OUTPUT UPDATED.Columns
      SET ColumnName = Expression
      VALUES(Value_1, Value_2, Value_X)
    2. UPDATE TableName
      SET ColumnName = Expression
      VALUES(Value_1, Value_2, Value_X)
      OUTPUT CHANGED.Columns
    3. UPDATE TableName
      SET ColumnName = Expression
      PRINT UPDATED ROWS
      VALUES(Value_1, Value_2, Value_X)
    4. UPDATE TableName
      SET ColumnName = Expression
      SHOW INSERTED.Columns
      FOR(Value_1, Value_2, Value_X)
    5. UPDATE TableName
      SET ColumnName = Expression
      OUTPUT INSERTED.Columns
      VALUES(Value_1, Value_2, Value_X)
  6. If you want to delete some record(s) and show the result when the operation ends, what formula would you use?
    1. DELETE FROM TableName
      OUTPUT REMOVED.Columns
      WHERE Condition(s)
    2. DELETE FROM TableName
      OUTPUT DELETED.Columns
      WHERE Condition(s)
    3. DROP FROM TableName
      OUTPUT REMOVED.Columns
      WHERE Condition(s)
    4. DELETE FROM TableName
      SHOW DELETED ROWS(Columns)
      WHERE Condition(s)
    5. DROP TableName
      OUTPUT DELETED.Columns
      WHERE Condition(s)

Answers

  1. Answers
    1. Wrong Answer: The UPDATE operator must indicate a table
    2. Right Anwer: That's the right formula
    3. Wrong Answer: There is no EDIT operator in SQL
    4. Wrong Answer: The UPDATE operator first indicates a table. Besides the assignment is not the only operation used to update a record
    5. Wrong Answer: There is no EDIT operator in SQL
  2. Answers
    1. Wrong Answer: That formula will delete the table itself, including its records
    2. Wrong Answer: There is no REMOVE operator in SQL
    3. Right Anwer: That's the right formula
    4. Wrong Answer: There is no stored procedure name sp_removetable
    5. Wrong Answer: There is no KILL FROM clause
  3. Answers
    1. Wrong Answer:
    2. Wrong Answer:
    3. Wrong Answer:
    4. Right Anwer: That's the right formula
    5. Wrong Answer:
  4. Answers
    1. Right Anwer: That's the right formula
    2. Wrong Answer:
    3. Wrong Answer:
    4. Wrong Answer:
    5. Wrong Answer:
  5. Answers
    1. Wrong Answer:
    2. Wrong Answer:
    3. Wrong Answer:
    4. Wrong Answer:
    5. Right Anwer: That's the right formula
  6. Answers
    1. Wrong Answer:
    2. Right Anwer: That's the right formula
    3. Wrong Answer:
    4. Wrong Answer:
    5. Wrong Answer:

Practice Questions

  1. Create

Previous Copyright © 2000-2013 FunctionX Next