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:
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.
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. 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:
The opttions can be set either visually or programmatically. Remember how to create a foreign key with code. Here is an example: void CreateTable(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("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) " +
");",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Persons\" has been created.",
"People",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
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.
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: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnCreateTables;
Button btnSelectRecords;
DataGridView dgvPersons;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateTables = new Button();
btnCreateTables.AutoSize = true;
btnCreateTables.Text = "Create Tables";
btnCreateTables.Location = new Point(12, 12);
btnCreateTables.Click += new EventHandler(CreateTables);
btnSelectRecords = new Button();
btnSelectRecords.AutoSize = true;
btnSelectRecords.Text = "Select Records";
btnSelectRecords.Location = new Point(120, 12);
btnSelectRecords.Click += new EventHandler(SelectRecords);
dgvPersons = new DataGridView();
dgvPersons.Location = new Point(12, 44);
dgvPersons.Size = new System.Drawing.Size(465, 145);
Controls.Add(btnCreateTables);
Controls.Add(btnSelectRecords);
Text = "People";
Controls.Add(dgvPersons);
Size = new System.Drawing.Size(500, 230);
StartPosition = FormStartPosition.CenterScreen;
dgvPersons.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void CreateTables(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Genders(" +
"GenderID int not null, " +
"Gender nvarchar(20), " +
"CONSTRAINT PK_Genders PRIMARY KEY(GenderID));",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Genders\" has been created.",
"People",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("INSERT INTO Genders " +
"VALUES(1, 'Male'), (2, 'Female'), (3, 'Unknown');",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A few records have been added to the Genders table.",
"People",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Persons" +
"(" +
"PersonID int identity(1,1) PRIMARY KEY NOT NULL, " +
"FirstName nvarchar(20), " +
"LastName nvarchar(20) NOT NULL, " +
"GenderID int CONSTRAINT FK_Genders " +
" FOREIGN KEY REFERENCES Genders(GenderID) " +
" ON DELETE NO ACTION" +
");",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Persons\" has been created.",
"People",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand(
"INSERT INTO Persons(FirstName, LastName, GenderID) " +
"VALUES('James', 'Palau', 1), " +
" ('Ann', 'Nsang', 2), " +
" ('Marc', 'Ulrich', 1), " +
" ('Arjuh', 'Namdy', 3), " +
" ('Aisha', 'Diabate', 2);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A few records have been added to the Persons table.",
"People",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
void SelectRecords(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Persons;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaPersons = new SqlDataAdapter(command);
BindingSource bsPersons = new BindingSource();
DataSet dsPersons = new DataSet("PersonsSet");
sdaPersons.Fill(dsPersons);
bsPersons.DataSource = dsPersons.Tables[0];
dgvPersons.DataSource = bsPersons;
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
Here is an example of showing all records of the table:
Now, if you try to delete one of the records of the Genders table, you would receive an error. Here is an example: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnDeleteGender;
Button btnSelectRecords;
DataGridView dgvPersons;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnSelectRecords = new Button();
btnSelectRecords.AutoSize = true;
btnSelectRecords.Text = "Select Records";
btnSelectRecords.Location = new Point(12, 12);
btnSelectRecords.Click += new EventHandler(SelectRecords);
btnDeleteGender = new Button();
btnDeleteGender.AutoSize = true;
btnDeleteGender.Text = "Delete Record";
btnDeleteGender.Location = new Point(120, 12);
btnDeleteGender.Click += new EventHandler(DeleteGender);
dgvPersons = new DataGridView();
dgvPersons.Location = new Point(12, 44);
dgvPersons.Size = new System.Drawing.Size(465, 145);
Controls.Add(btnSelectRecords);
Controls.Add(btnDeleteGender);
Text = "People";
Controls.Add(dgvPersons);
Size = new System.Drawing.Size(500, 230);
StartPosition = FormStartPosition.CenterScreen;
dgvPersons.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void SelectRecords(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Persons;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaPersons = new SqlDataAdapter(command);
BindingSource bsPersons = new BindingSource();
DataSet dsPersons = new DataSet("PersonsSet");
sdaPersons.Fill(dsPersons);
bsPersons.DataSource = dsPersons.Tables[0];
dgvPersons.DataSource = bsPersons;
}
}
void DeleteGender(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='People';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("DELETE FROM Genders " +
"WHERE GenderID = 2;",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("The second gender has been deleted.",
"People - Gender",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
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. 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 NO ACTION
ON UPDATE NO ACTION
);
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
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.
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
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.
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
|
|||||||||||||||||||||||||||||
