Home

Introduction to Microsoft SQL Server Relational Databases

   

The Primary Key Constraint

 

Relational Databases

A relational database is a system in which information flows from one database object to another. For example, if you create an application used to process orders for a car rental business, you can create one table for the cars and a separate table used to process customers orders. When processing an order, you would want to simply select a car in the order processing table. That way, you would avoid entering new information about a particular car every time it is rented. If you do this, you may have one order that has a car named Toyota Corolla with the tag number FFG802 and another order with the car Toyoda Corolla with the tag number FFF802 when in fact both orders refer to the same car. Therefore, you should avoid any chance to type the information for the car when processing an order.

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 as the "key" of the table. Such a column is referred to as 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 an car table of a car rental company can be set on a Tag Number field because each car should have a unique tag number. A table can also use more than one column to represent the primary key if you judge it necessary.

Once you have decided that a table will have a primary key, you must decide what type of data that field 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 varchar 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. Such a field should have an int data type.

Introduction to Creating a Primary Key

To create a primary key column using SQL, the first thing to do is, on the right side of the column definition, type PRIMARY KEY. Here is an example:

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);

The Primary Key Constraint

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 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  NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);

The Foreign Key Constraint

 

Introduction

Continuing with our car rental database, imagine a customer comes to rent a car. We already established that it would be redundant to create new car information every time you process a new customer order. Instead, you would get the car's information from the table that holds data for the cars, and provide that information to the table used to process orders. As we described earlier, the car 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 does not 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.

Creating a Foreign Key

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. The basic formula to  create a foreign key in the SQL 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 PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);

The Foreign Key Constraint

Based on the above technique, notice that the foreign key does not have an object name as we saw for the primary key. If you do not 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 PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID int NULL CONSTRAINT FKGenders
                       FOREIGN KEY REFERENCES Genders(GenderID)
);

You can also create a foreign key as its own constraint, which is another technique to name a foreign key. To assign a desired name to a foreign key, you must create it as a constraint. To do this, after defining the column that holds the foreign key before the end of defining the table, create a constraint using the following formula:

CONSTRAINT Name FOREIGN KEY(Foreign Key) REFERENCES Parent(Foreign Key)

Here is an example:

CREATE TABLE Genders
(
    GenderID int NOT NULL,
    Gender nvarchar(20) NOT NULL,
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int,
    Comments nvarchar(max),
    CONSTRAINT FK_Genders FOREIGN KEY(GenderID) REFERENCES Genders(GenderID)
);

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.

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. 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 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 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 to take no action. 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:

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

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:

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;
    }
}

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. Here is an example:

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
);

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 apply it programmatically, add CASCADE after ON DELETE or ON UPDATE. Here is an example:

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

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 set it 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

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

Constraints Maintenance

 

Introduction

If you decide to delete a table, first check if it involves in a relationship. 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 not null PRIMARY KEY,
    Gender nvarchar(20)
);
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)
);
GO

INSERT INTO Genders
VALUES(1, N'Make'), (2, N'Female'), (3, N'Unknown');
GO
INSERT INTO Persons(PersonID, FirstName, LastName, GenderID)
VALUES(1, N'Peter', N'Mukoko', 1), (2, 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. 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)
);

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;

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);

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 not null PRIMARY KEY,
    Gender nvarchar(20)
);

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);

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

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

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);

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.

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:

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 is neither an expression nor a function. A check constraint contains an expression and may contain a function as part of its definition. 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.

 
 
   
 

Previous Copyright © 2014, FunctionX Next