ADO.NET With Microsoft Visual C#: The Uniqueness of Records

	Microsoft Visual C#: The Uniqueness of Records

Introduction

One of the primary concerns of records is their uniqueness. In a professional database, you usually want to make sure that each record on a table can be uniquely identified. Microsoft SQL Server provides many means of taking care of this. These include the identity column, the primary key, and the indexes. One way to do this is to apply a uniqueness rule on a column.

Visually Creating a Uniqueness Rule

To visually specify that each value of a column must be unique, in the Table window, right-click the desired column and click Indexes/Keys... In the Indexes/Keys dialog box, click Add. On the right side, set the Is Unique field to Yes:

Uniqueness

After doing this, click Close.

Programmatically Creating a Uniqueness Rule

To assist you with creating a columns whose values will be distinguishable, Transact-SQL provides the UNIQUE keyword. To apply it on a column, after the data type, type UNIQUE. Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ExoDB1
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnDatabase_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = 
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand command =
                    new SqlCommand("CREATE TABLE Students" +
                                   "(" +
                                   "    StudentNumber int UNIQUE," +
                                   "    FirstName nvarchar(50)," +
                                   "    LastName nvarchar(50) NOT NULL" +
                                   ");",
                           connection);
                connection.Open();
                command.ExecuteNonQuery();

                MessageBox.Show("A new table named Students has been created.");
            }
        }
    }
}

When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error:

CREATE TABLE Students
(
    StudentNumber int UNIQUE,
    FirstName nvarchar(50),
    LastName nvarchar(50) NOT NULL
);
GO

INSERT INTO Students
VALUES(24880, N'John', N'Scheels'),
      (92846, N'Rénée', N'Almonds'),
      (47196, N'Peter', N'Sansen'),
      (92846, N'Daly', N'Camara'),
      (36904, N'Peter', N'Sansen');
GO

By the time the fourth record is entered, since it uses a student number that exists already, the database engine would produce an error:

Violation of UNIQUE KEY constraint 'UQ__Students__DD81BF6C145C0A3F'. Cannot insert duplicate key in object 'dbo.Students'. The statement has been terminated.


Home	Copyright © 2010-2016, FunctionX