HandsOn Using a Check Constraint to Specify a Condition for All Values Entered for the Column

1. Open the Acc2003_Chap20.mdb file from the book's downloadable files, or create this file from scratch using the Access user interface.

2. Switch to the Visual Basic Editor window and insert a new module.

3. In the module's Code window, enter the CheckColumnValue procedure as shown below.

Sub CheckColumnValue()

Dim conn As ADODB.Connection Dim strTable As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblAwards"

conn.Execute "CREATE TABLE " & strTable & _

"(Id AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY," & _ "YearsWorked INT, CONSTRAINT FromTo " & _ "CHECK (YearsWorked BETWEEN 1 AND 30));"

ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

4. Run the above procedure to create the tblAwards table with the CHECK constraint.

5. Open the tblAwards table and enter a value that does not fall between 1 and 30 in the YearsWorked column. You should receive the message shown in Figure 20-1.

Programming with the Jet Data Definition Language

Figure 20-1: This message appears when you attempt to enter a value in the YearsWorked column that is not within the range specified by the FromTo constraint (see Hands-On 20-1).

Figure 20-1: This message appears when you attempt to enter a value in the YearsWorked column that is not within the range specified by the FromTo constraint (see Hands-On 20-1).

The next hands-on procedure demonstrates how to create a CHECK constraint to ensure that the values of the Items column in the tblBookOrders table is less than the value of the MaxUnits column in the tblSupplies table for the specified ISBN number. This hands-on also illustrates using the SQL Data Manipulation Language (DML) statements INSERT INTO, BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.

0 0

Post a comment