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:
If the value is not appropriate:
You create a check constraint at the time you are creating a table.
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:
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:
After creating the check constraints, you can click OK. 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: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnCreateTable;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateTable = new Button();
btnCreateTable.AutoSize = true;
btnCreateTable.Text = "Select Records";
btnCreateTable.Location = new Point(12, 12);
btnCreateTable.Click += new EventHandler(CreateTable);
Text = "Kolo Bank";
Controls.Add(btnCreateTable);
}
void CreateTable(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='Exercise2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Employees(" +
"[Employee Number] nchar(7)," +
"[Full Name] varchar(80)," +
"[Hourly Salary] smallmoney," +
"CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50));",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named Employees has been deleted.",
"Exercise",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
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, 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.
|
|||||||||||||||||||||||
