Home

Introduction to Records

  

Introduction

The records of a database are stored in tables. To visually add a record to a table, in the Object Explorer, expand the database and the Tables node. Right-click the table and click Edit Top 200 Rows. If no record exists in the table, it would appear with an empty row of cells marked NULL:

Table

To perform data entry on a table, you can click in a field and type the appropriate value.

To programmatically perform data entry, you use a Data Definition Language (DDL) command known as INSERT. The DDL command to perform data entry is INSERT combined with VALUES. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);

An alternative is to add the INTO keyword after the INSERT keyword:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an existing table in the database you are using.

The VALUES keyword is followed by parentheses. In the parentheses, type the desired values:

  • If the column is a BIT data type, you must specify one of its values as 0 or 1.
  • If the column is an integer type, provide a valid natural number without the decimal separator.
  • If the column is a decimal type, type the value with its character separator
  • If the column is a time or a date type, provide a valid date

Adjacent Data Entry

To perform adjacent data entry, you must follow the sequence of fields of the table. Here is an example:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15);
GO

In the same way, you can create different INSERT sections for each record. Here is an example:

USE Exercise;
Go

INSERT INTO Employees
VALUES(N'204815', N'Jeanne Swanson', N'19980802', 18.48);
GO
INSERT INTO Employees
VALUES(N'824460', N'Ponce Valley', N'20041208', 22.25);
GO
INSERT INTO Employees
VALUES(N'495007', N'Gina Sow', N'20000622', 16.85);
GO

Instead of writing the INSERT expression for each record, you can write it once, followed by  VALUES. Then enter each record in its parentheses. The parentheses are separated by commas. Here is an example:

USE Exercise;
Go

INSERT INTO Employees
VALUES(N'595002', N'John Meah', N'20000212', 32.25),
      (N'928375', N'Chuck Stansil', N'20080628', 20.05),
      (N'792764', N'Orlando Perez', N'20000616', 12.95),
      (N'290024', N'Anne Nguyen', N'20090428', 25.75);
GO

Random Data Entry

To perform data entry in an order of your choice, provide a list of the fields of the table. Here is an example:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO Employees(EmployeeNumber, EmployeeName, DateHired, HourlySalary)
VALUES(N'927957', N'Helen Gooding', N'19961220', 22.65);
GO

In the same way, you can create different INSERT sections for each record and each INSERT expression can have its own list of columns. Here are examples:

USE Exercise;
Go

INSERT INTO Employees(DateHired, EmployeeName, EmployeeNumber, HourlySalary)
VALUES(N'20081028', N'June Santos', N'729475', 24.85);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Ann Pulley', N'300293', N'20020520');
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Annie Pastore', N'972405', N'19941122');
GO
INSERT INTO Employees(EmployeeNumber, HourlySalary)
VALUES(N'490007', 12.95);
GO

If you want to create a series of records that use the same sequence of fields, write the INSERT keyword or INSERT INTO expression, followed by the name of the table, followed by parentheses that contain the list of fields, and followed by  VALUES. Then enter each record in its parentheses. The parentheses are separated by commas. Here is an example:

USE Exercise;
Go

INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112'),
      (N'Paul Handsome', N'720947', N'20000802'),
      (N'Gina Palau', N'247903', N'20080612');
GO

INSERT INTO Employees(DateHired, EmployeeName, EmployeeNumber, HourlySalary)
VALUES(N'20091124', N'Arnold Futah', N'222475', 22.75);
GO

Outputting the Insertion Result

In the techniques we have used so far, when or if the records have been added to a table, whether the operation was successful or not, we had no way of finding out. One way you can get this information is to store the inserted records in another table. To support this, Transact-SQL provides the OUTPUT operator. The formula to use it is:

INSERT INTO TableName
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value_X)

You start with the normal record insertion with the INSERT INTO TableName expression. This is followed by the OUTPUT operator followed by the INSERTED operator and a period. If you are adding a value for each record, follow the period with *. The statement continues with the VALUES operator that is followed by parentheses in which you list the values to be added to the table. Here is an example:

USE VideoCollection;
GO

CREATE TABLE Videos
(
	Title nvarchar(50), 
	Director nvarchar(50),
	WideScreen bit,
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
OUTPUT inserted.*
VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R', 2001),
      (N'Memoirs of a Geisha', N'Rob Marshall', 1, N'PG-13', 2006),
      (N'Last Castle (The)', N'Rod Lurie', 1, N'', 2001),
      (N'Sneakers', N'Phil Alden Robinson', 1, N'PG-13', 2003);
GO

When this statement executes, if you are working in the Microsoft SQL Server Management Studio, the lower part would display a list of the records that were added:

OUTPUT

If you use the above formula, when you close the database, the reference is lost. If you want to store the list of newly created in a table, on the right side of the INSERTED operator and its period, type INTO followed by the name of the table that will receive the values. The table must have been created; that is, it must exist at the time this inserted operation is taking place. Here is an example:

USE VideoCollection;
GO

CREATE TABLE Archives
(
	Title nvarchar(50), 
	Director nvarchar(50),
	WideScreen bit,
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
OUTPUT inserted.* INTO Archives
VALUES(N'Two for the Money', N'D.J. Caruso', 1, N'R', 2006),
      (N'Wall Street', N'Oliver Stone', 0, N'R', 2000);
GO

In this case, a copy of the newly created records would be stored in the indication table.

The above techniques assume that you are adding a complete record; that is, you are providing a value for each column of the table. We already saw that if you want to provide values for only some columns, after the name of the table, provide the list of columns in parentheses. To get the list of newly inserted records, after the OUTPUT keyword, type INSERTED followed by a period and followed by the name of the first column. Do this for each column. The formula to use is:

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X
VALUES(Value_1, Value_2, Value_X)

Of course, you can list the columns in an order of your choice, as long as both the TableName and the OUTPUT section use the exact same order. Here is an example:

USE VideoCollection;
GO

INSERT INTO Videos(Director, Rating, Title)
OUTPUT inserted.Director, inserted.Rating, inserted.Title
VALUES(N'Jonathan Lynn', N'R', N'Distinguished Gentleman (The)'),
      (N'Paul Anderson', N'R', N'Soldier');
GO

In this case, when the statement has executed, the result would display in the lower portion of the Microsoft SQL Server Management Studio. If you want to store the result in a table, use the following formula

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X INTO TargetTable
VALUES(Value_1, Value_2, Value_X)

Here is an example:

USE VideoCollection;
GO

CREATE TABLE Entertainment
(
	Title nvarchar(50), 
	Director nvarchar(50)
)
GO

INSERT INTO Videos(Title, Director)
OUTPUT inserted.Title, inserted.Director INTO Entertainment
VALUES(N'Michael Jackson Live in Bucharest', N'Andy Morahan'),
      (N'Outfoxed', N'Robert Greenwald');
GO

Assistance With Data Entry: Using Expressions

 

Introduction

There are various ways you can assist the user with data entry. Besides using a function, you can create an expression using operators such as those we reviewed in lessons 3 and 5. You can create an expression when creating a table, whether in the Table window or using SQL in a query window.

Visually Creating an Expression

To create an expression when visually creating a table, in the top section, specify the column's name (only the column name is important). In the bottom section, expand the Computed Column Specification field and, in its (Formula) field, enter the desired expression. Here is an example:

Creating a SQL Expression

You can also create an expression in SQL expression you are using to create a table. To do this, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example:

CREATE TABLE Circle
(
    CircleID int identity(1,1) NOT NULL,
    Radius decimal(8, 3) NOT NULL,
    Area AS Radius * Radius * PI()
);
GO
 

Using an Expression During Data Entry

When performing data entry, you must not provide a value for a column that has an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:

INSERT INTO Circle(Radius) VALUES(46.82);
GO
INSERT INTO Circle(Radius) VALUES(8.15);
GO
INSERT INTO Circle(Radius) VALUES(122.57);
GO
 

 

 

Assistance with Data Entry: The Nullity of a Field

 

Introduction

During data entry, users of your database will face fields that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not (or cannot) provide it. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it.

A field is referred to as null when no data entry has been made to it:

  • Saying that a field is null doesn't mean that it contains 0 because 0 is a value
  • Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value

A field is referred to as null if there is no way of determining the value of its content (in reality, the computer, that is, the operating system, has its own internal mechanism of verifying the value of a field) or its value is simply unknown. As you can imagine, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table.

A field is referred to as required if the user must provide a value for it before moving to another record. In other words, the field cannot be left empty during data entry.

To solve the problem of null and required fields, Microsoft SQL Server proposes one of two options: allow or not allow null values on a field. For a typical table, there are pieces of information that the user should make sure to enter; otherwise, the data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, that is, to require the value, if you are visually creating the table, clear the Allow Nulls check box for the field. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, check its Allow Nulls check box.

NULL or NOT NULL?

If creating a table using SQL, to specify that it can allow null values, type NULL on the right side of the column. To specify that the values of the column are required, on the right side, type NOT NULL. If you don't specify NULL or NOT NULL, the column will be created as NULL. Here are examples:

CREATE TABLE Persons
(
    FirstName varchar(20) NULL,
    LastName varchar(20) NOT NULL,
    Gender smallint
);
GO

If the table was already created and it holds some values already, you cannot set the Allow Nulls option on columns that don't have values.

After specify that a column would NOT allow NULL values, if the user tries creating a record but omits to create a value for the column, an error would display. Here is an example:

No row was updated

This error message box indicates that the user attempted to submit a null value for a column. To cancel the action, you can press Esc.

Practical LearningPractical Learning: Applying Fields Nullity

  1. In the Object Explorer, right-click Countries in the WorldStatistics node and click Modify
  2. Apply the nullity of fields as follows:
     
  3. Save the table

Assistance with Data Entry: The Default Value of a Column

 

Introduction

Sometimes most records under a certain column may hold the same value although just a few would be different. For example, if a school is using a database to register its students, all of them are more likely to be from the same state. In such a case, you can assist the user by automatically providing a value for that column. The user would then simply accept the value and change it only in the rare cases where the value happen to be different. To assist the user with this common value, you create what is referred to as a default value.

Visually Creating a Default Value

You can create a default value of a column when creating a table. To specify the default value of a column, in the top section, click the column. In the bottom section, click Default Value or Binding, type the desired value following the rules of the column's data type:

It the Data Type is Intructions
Text-based (char, varchar, text, and their variants) Enter the value in single-quotes
Numeric-based Enter the value as a number but following the rules of the data type.
For example, if you enter a value higher than 255 for a tinyint, you would receive an error
Date or Time Enter the date as either MM/DD/YYYY or YYYY/MM/DD. You can optionally include the date in single-quotes.
Enter the time following the rules set in the Control Panel (Regional Settings).
Bit Enter True or False
 

Programmatically Creating a Default Value

To specify the default value in a SQL statement, when creating the column, after specifying the other pieces of information of the column, type DEFAULT followed by an empty space and followed by the desired value. Here are examples:

CREATE TABLE Employees
(
    FullName VARCHAR(50),
    Address VARCHAR(80),
    City VARCHAR(40),
    State VARCHAR(40) DEFAULT 'NSW',
    PostalCode VARCHAR(4) DEFAULT '2000',
    Country VARCHAR(20) DEFAULT 'Australia'
);
GO

After creating the table, the user does not have to provide a value for a column that has a default. If the user does not provide the value, the default would be used when the record is saved.

Author Note If the user provides a value for a column that has a default value and then deletes the value, the default value rule would not apply anymore: The field would simply become empty
 

Practical LearningPractical Learning: Assigning a Default Value to a Column

  1. Display the PowerShell window
  2. To change the database, type the following and press Enter at the end:
     
    USE CeilInn1;
    GO
  3. To create a new table whose columns have default values, type the following and press Enter at the end:
     
    CREATE TABLE Rooms (
        RoomNumber nvarchar(10),
        RoomType nvarchar(20) default N'Bedroom',
        BedType nvarchar(40) default N'Queen',
        Rate money default 75.85,
        Available bit default 0
    );
    GO
  4. To perform data entry on the new table, type the following and press Enter at the end:
     
    INSERT INTO Rooms(RoomNumber) VALUES(104);
    GO
  5. To add another record to the new table, type the following:
     
    INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
               VALUES(105, N'King', 85.75, 1),
    		 (106, N'King', 85.75, 1)
    GO
  6. To add another record, type the following:
     
    INSERT INTO Rooms(RoomNumber, Available) VALUES(107, 1)
    GO
  7. To add another record, type the following:
     
    INSERT INTO Rooms(RoomNumber, BedType, Rate) VALUES(108, N'King', 85.75)
    GO
  8. To add another record, type the following:
     
    INSERT INTO Rooms(RoomNumber, Available) VALUES(109, 1)
    GO
  9. To add one more record, type the following:
     
    INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, Available)
    	   VALUES(110, N'Conference', N'', 450.00, 1)
    GO
  10. Return to Microsoft SQL Server Management Studio

Assistance with Data Entry: Identity Columns

 

Introduction

One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Category Item Name Size Unit Price
Women Long-sleeve jersey dress Large 39.95
Boys Iron-Free Pleated Khaki Pants S 39.95
Men Striped long-sleeve shirt Large 59.60
Women Long-sleeve jersey dress Large 45.95
Girls Shoulder handbag   45.00
Women Continental skirt Petite 39.95

Imagine that you want to change the value of an item named Long-sleeve jersey dress. Because you must find the item programmatically, you can start looking for an item with that name. This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named Women. In the same way, there are too many records that have a Large value in the Size column, same thing problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve shirt.

To solve the problem of uniquely identifying a record, you can create a particular column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user doesn't have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column.

You cannot create an identity column one an existing table, only on a new table.

Visually Creating an Identity Column

To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column resembles that of the table but in singular. Also, by habit, the name of the column ends with _id, Id, or ID.

After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

Practical LearningPractical Learning: Creating an Identity Column

  1. In the Object Explorer, under WorldStatistics, right-click Tables and click New Table...
  2. Set the name of the column to ContinentID and press Tab
  3. Set its data type to int and press F6.
    In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes
  4. Complete the table as follows:
     
    Column Name Data Type Allow Nulls
    ContinentID    
    Continent varchar(80) Unchecked
    Area bigint  
    Population bigint  
  5. Save the table as Continents

Creating an Identity Column Using SQL

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

CREATE TABLE StoreItems(
ItemID int IDENTITY(1, 1) NOT NULL, 
Category varchar(50),
[Item Name] varchar(100) NOT NULL,
Size varchar(20),
[Unit Price] money);
GO

Practical LearningPractical Learning: Creating an Identity Column Using SQL

  1. Display the PowerShell window
  2. Type the following:
     
    USE CeilInn1;
    GO
  3. To create a table with an identity column, type the following and press Enter after each line:
     
    DROP TABLE Rooms;
    GO
    
    CREATE TABLE Rooms (
        RoomID int identity(1, 1) NOT NULL,
        RoomNumber nvarchar(10),
        RoomType nvarchar(20) default N'Bedroom',
        BedType nvarchar(40) default N'Queen',
        Rate money default 75.85,
        Available bit default 0
    );
    GO
  4. To perform data entry on the new table, type the following and press Enter at the end:
     
    INSERT INTO Rooms(RoomNumber) VALUES(104);
    GO
  5. To add another record to the new table, type the following:
     
    INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
               VALUES(105, N'King', 85.75, 1),
    		 (106, N'King', 85.75, 1)
    GO
  6. To add another record, type the following:
     
    INSERT INTO Rooms(RoomNumber, Available) VALUES(107, 1)
    GO
  7. To add another record, type the following:
     
    INSERT INTO Rooms(RoomNumber, BedType, Rate) VALUES(108, N'King', 85.75)
    GO
  8. To add another record, type the following:
     
    INSERT INTO Rooms(RoomNumber, Available) VALUES(109, 1)
    GO
  9. To add one more record, type the following:
     
    INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, Available)
    	   VALUES(110, N'Conference', N'', 450.00, 1)
    GO
  10. Return to Microsoft SQL Server Management Studio

Assistance with Data Entry: 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 is unique. Microsoft SQL Server provides many means of taking care of this. These include the identity column, the primary key, and the indexes. We will review these issues in later lessons. Still, one way to do this is to apply a uniqueness rule on a column.

Creating a Uniqueness Rule

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

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

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;
GO
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:

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.

Practical LearningPractical Learning: Applying Uniqueness to a Column

  1. In the PowerShell window, to create a new table that has a uniqueness rule on a column, type the following:
     
    CREATE TABLE Customers (
        CustomerID int identity(1, 1) NOT NULL,
        AccountNumber nchar(10) UNIQUE,
        FullName nvarchar(50)
    );
    GO
  2. To perform data entry on the table, type the following and press Enter at the end of each line:
     
    INSERT INTO Customers(AccountNumber, FullName)
    	       VALUES(395805, N'Ann Zeke'),
    	             (628475, N'Peter Dokta'),
    	             (860042, N'Joan Summs')
    GO
  3. To try adding another record to the table, type the following and press Enter at the end of each line:
     
    USE CeilInn1;
    GO
    
    INSERT INTO Customers(AccountNumber, FullName)
    	       VALUES(628475, N'James Roberts')
    GO
  4. Notice that you receive an error

Assistance With Data Entry: 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.

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:

CREATE TABLE Employees
(
	[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;
    GO
    
    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))
    );
    GO
  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'pdorka1900@hotmail.com'),
          (N'860042', N'Joan Summs', N'410-114-6820', N'jsummons@emailcity.net');
    GO
  3. To try adding a new record to the table, type the following:
     
    INSERT INTO Customers(AccountNumber, FullName)
    	       VALUES(N'228648', N'James Roberts')
    GO
  4. Notice that you receive an error.
    Close the PowerShell window

Assistance With Data Entry: Using Functions

 

Introduction

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:

CREATE TABLE RepairOrders
(
  RepairID int Identity(1,1) NOT NULL,
  CustomerName varchar(50),
  CustomerPhone varchar(20),
  RepairDate datetime2
);
GO

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());
GO
INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)
	    VALUES(N'(240) 601-3795', N'Paulino Santiago', GETDATE());
GO
INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)
	    VALUES(N'Alicia Katts', GETDATE(), N'(301) 527-3095');
GO
INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)
	    VALUES(GETDATE(), N'703-927-4002', N'Bertrand Nguyen');
GO

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.

Other Features of Data Entry

 

Is RowGuid

This property allows you to specify that a column with the Identity property set to Yes is used as a ROWGUID column.

Collation

Because different languages use different mechanisms in their alphabetic characters, this can affect the way some sort algorithms or queries are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table.

To find out what language your server is currently using, in a Query window or from PowerShell, you can type:

SELECT @@LANGUAGE;
GO 

Lesson Summary

   

Topics Reviewed

 

Topics Reviews

  • Record
  • Row
  • Table Navigation
  • Visual Data Entry
  • SQL Data Entry
  • Adjacent Data Entry
  • Random Data Entry
  • Default Values
  • Identity Columns
  • Expressions
  • Check Constraints
  • Collation
  • Data Import
  • Selecting Records
  • Editing Records
  • Updating Records
  • Deleting Records

Keywords, Operators, and Properties

  • NULL
  • NOT NULL
  • DEFAULT
  • IDENTITY
  • Identity Specification
  • (Is Identity)
  • Identity Seed property
  • Identity Increment
  • CONSTRAINT
  • CHECK
  • Collation
  • databases
  • EXISTS
  • UPDATE
  • DELETE
 
 
   
 

Previous Copyright © 2009-2013 FunctionX, Inc. Next