Unique Values on a Column



You may want a column to receive a unique value for each of its records.

To specify that a column will require unique values, when creating it in SQL, use the UNIQUE keyword.

Here is an example:

USE Exercise;
    StudentNumber int UNIQUE,
    FirstName nvarchar(50),
    LastName nvarchar(50) NOT NULL

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:

USE Exercise;
    StudentNumber int UNIQUE,
    FirstName nvarchar(50),
    LastName nvarchar(50) NOT NULL

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

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

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__Students__DD81BF6C145C0A3F'. 
Cannot insert duplicate key in object 'dbo.Students'.
The statement has been terminated.



Assistance With Data Entry: Check Constraints



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

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:

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

An Error From an Invalid Value of Check Constraint

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.

Practical LearningPractical Learning: Creating a Check Constraint

  1. In the PowerShell window, to create a table that has a check mechanism, type the following:
    DROP TABLE Customers;
    CREATE TABLE Customers (
        CustomerID int identity(1, 1) NOT NULL,
        AccountNumber nchar(10) UNIQUE,
        FullName nvarchar(50) NOT NULL,
        PhoneNumber nvarchar(20),
        EmailAddress nvarchar(50),
        CONSTRAINT CK_CustomerContact
    	CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL))
  2. To add records to the new table, type the following:
    INSERT INTO Customers(AccountNumber, FullName,
                          PhoneNumber, EmailAddress)
    VALUES(N'395805', N'Ann Zeke', N'301-128-3506', N'azeke@yahoo.jp'),
          (N'628475', N'Peter Dokta', N'(202) 050-1629', 
          (N'860042', N'Joan Summs', N'410-114-6820', N'jsummons@emailcity.net');
  3. To try adding a new record to the table, type the following:
    INSERT INTO Customers(AccountNumber, FullName)
    	       VALUES(N'228648', N'James Roberts')
  4. Notice that you receive an error.
    Close the PowerShell window

Assistance With Data Entry: Using Functions



You can involve a function during data entry. As an example, you can call a function that returns a value to assign that value to a column. You can first create your own function and use it, or you can use one of the built-in functions.

Using Functions

In order to involve a function with your data entry, you must have and identity one. You can use one of the built-in functions of Transact-SQL. You can check one of the functions we reviewed in Lesson 8. Normally, the best way is to check the online documentation to find out if the assignment you want to perform is already created. Using a built-in function would space you the trouble of getting a function. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers:

  RepairID int Identity(1,1) NOT NULL,
  CustomerName varchar(50),
  CustomerPhone varchar(20),
  RepairDate datetime2

When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples:

INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate)
	    VALUES(N'Annette Berceau', N'301-988-4615', GETDATE());
INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)
	    VALUES(N'(240) 601-3795', N'Paulino Santiago', GETDATE());
INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)
	    VALUES(N'Alicia Katts', GETDATE(), N'(301) 527-3095');
INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)
	    VALUES(GETDATE(), N'703-927-4002', N'Bertrand Nguyen');

You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.

If none of the Transact-SQL built-in functions satisfies your requirements, you can create your own, using the techniques we studied in Lesson 7.


Previous Copyright © 2009-2016, FunctionX, Inc. Next