Using Rules to Enforce Data Integrity – Some Examples

There are many aspects involved in creating a well-designed database that insure the integrity of the data that it contains. The proper use of data types, identity columns, nullability, defaults, primary keys, and foreign keys all go a long way in making sure that the business rules for the database are maintained. As part of this concept of data integrity is the idea of a domain. A domain is a set of all the valid values that a column might have.

One of the ways to enforce a domain is by creating a look up table to support a column in a primary table and assigning a foreign key to that column which references the look up table. This insures that the data for that column can only be selected from the target table or remain null if Null is allowed. Sometimes creating a lookup table is not practical. For instance, if I wanted to insure that the user entered a valid zip code number, I would not create a lookup table of every zip code value that was valid because the valid range for a zip code is 00601 through 99950. Obviously, a more practical plan must be devised. Let’s look at two possible scenarios to solve this problem by creating user defined fields and rules. Then we will discuss one more example of how to validate an employee social security number.

Example #1 – the zip code

Scenario # 1:

1. Define a user defined field ‘zip1_usa of type integer that allows a NULL value

       sp_addtype  ‘zip1_usa’, INTEGER, NULL

2. If you wish, you can create a default value for the user-defined type .

       CREATE DEFAULT zip_default as  60076

3. Bind the default to the user-defined type

        sp_bindefault ‘zip_default’ , ‘zip1_usa’

   At this point , the user-defined type is no better than defining a field as integer with a default value.

4. create a rule to limit the valid range of values

       CREATE RULE zip1_usa_rule
   
        AS @number > 600 and @number < 99951
   

5. bind the rule to the user-defined field

        sp_bindrule  ‘zip1_usa_rule’ , ‘zip1_usa’

6. Create a table called tblzip1 . Define a column called zip1 and assign the data type ‘zip1_usa’ to it.
7. attempt to insert assorted data to test the domain – see below

Try It Out:

insert into tblzip1 ( zip1 )  values ( 600 )

Results:

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblzip1’, column ‘zip1’.

The statement has been terminated.

insert into tblzip1 ( zip1 )  values ( 601 )

Results :

(1 row(s) affected)

insert into tblzip1 (zip1) values( 00799 )

Results :

(1 row(s) affected

insert into tblzip1 ( zip1 )  values ( 60077 )

Results :

(1 row(s) affected)

insert into tblzip1   (zip1) values ( 99951 )

Results:

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblzip1’, column ‘zip1’.

The statement has been terminated.

select * from tblzip1

Results:

zip1

———–

601

799

60077

(3 row(s) affected)

While the first scenario meets some of the criteria of limiting the values of the zip code, it is not very appealing because we would like every zip code to contain 5 digits. By using an integer, we lose the ability to have leading zeros as part of the data as can be seen when we insert 00799 into the table.

Scenario # 2 :

1. define a user defined field ‘zip2_usa’ of type variable character with a size of 5 that allows a NULL value

               sp_addtype ‘zip2_usa’, ‘varchar (5)’, ‘NOT NULL’

2. create a rule to limit the valid range of values
   

   
   
       CREATE RULE zip2_usa_rule
   
        AS @string > ‘00600’
   
       and @string < '99951'
       and len(@string) = 5 
   

   By checking the length of the string , we force the end user to enter a 5 character zip code.

3. bind the rule to the user-defined field

        sp_bindrule  ‘zip2_usa_rule’ , ‘zip2_usa’

4. Create a table called tblzip2 . Define a column called zip2 and assign the data type ‘zip2_usa’ to it.
5. attempt to insert assorted data to test the domain – see below

Try It Out:

insert into tblzip2 ( zip2 )  values ( ‘600’ )

Results:

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblzip2’, column ‘zip2’.

The statement has been terminated.

insert into tblzip2 ( zip2 )  values ( ‘601’ )

Results :

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblzip2’, column ‘zip2’.

The statement has been terminated.

Note: This time the insert failed because of the length of the string.

insert into tblzip2 ( zip2 )  values ( ‘00601’ )

Results :

(1 row(s) affected)

insert into tblzip2  (zip2) values( ‘00799’ )

Results :

(1 row(s) affected

insert into tblzip2 ( zip2 )  values ( ‘60077’ )

Results :

(1 row(s) affected)

insert into tblzip2 values ( ‘a1234’ )

Results:

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblzip2’, column ‘zip2’.

The statement has been terminated.

insert into tblzip2  ( zip2 )  values ( 99951 )

Results:

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblzip2’, column ‘zip2’.

The statement has been terminated.

select * from tblzip2

Results:

zip2

—–

00601

00799

60077

(3 row(s) affected)

Note: We maintain our leading zeroes and always have 5 numeric characters by using this scenario.

Example #2 – Social Security number

Let’s try one more example. In this case we need to validate an employee’s social security number to insure that it has 9 digits and they are all numeric.

1. define a user defined field ’emp_id’ of type character with a size of 9 that does not allow a NULL value

               sp_addtype ’emp_id’, ‘char (9)’, ‘NOT NULL’

2. create a rule to limit the valid range of values for the social security
   

   
   
       CREATE RULE  validate_emp_id
   
        AS @value LIKE ‘[0-9][0-9][0-9][0-9][0-
   
   9][0-9][0-9][0-9][0-9]’
   
   

   By using the ‘LIKE’ operator we ensure that the field will be 9 numeric characters with each character falling in the range between 0 and 9.

3. bind the rule to the user-defined field

        sp_bindrule validate_emp_id , emp_id

4. Create a table called tblemp . Define a column called emp_num and assign the data type ’emp_id’ to it.
5. attempt to insert assorted data to test the domain – see below

Try It Out:

insert into tblemp values( ‘123’ )

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblemp’, column ’emp_num’.

The statement has been terminated.
insert into tblemp values( ‘123456789’ )

Results:

         (1 row(s) affected)

insert into tblemp values( ‘a12345678’ )

Results:

Server: Msg 513, Level 16, State 1, Line 1

A column insert or update conflicts with a rule imposed

by a previous CREATE RULE statement. The statement was

terminated. The conflict occurred in database

‘rules_demo’, table ‘tblemp’, column ’emp_num’.

The statement has been terminated.

In this article , we have shown how by using user-defined fields and assigning rules to them, we can extend our ability to insure data integrity by using domains.