Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 28, 2001

Using Rules to Enforce Data Integrity - Some Examples

By Steve Mandel

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date