AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com 


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

Create an index on a field that will not accept nulls

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Discussion:

In this scenario, we would like a field to be indexed, not accept duplicate values, accept zero-length strings, and not accept null values at the same time.  This piece of code becomes even stronger when you want to apply it with multi-field indexes.  For example, you might have a situation in which you would like to have the combination of two fields to be indexed, disallow duplicates, and at the same time not to accept nulls.  With indexes, we can apply data validation rules on the combined values of fields.  In this scenario, we work with one field to keep it simple.   

 

Code:

CREATE UNIQUE INDEX indLastName ON tblCustomer1 (LastName) WITH DISALLOW NULL;

 

Result:

As you can see the LastName field is indexed, accepts no duplicates (UNIQUE) and it will require an entry (DISALLOW NULL).  Pay attention because Access 2007 does not indicate a field as required in the table design view when we use an index constraint.  In the picture of the table design view below, you see that the field last name looks like it is not required while it is.  Try to go to data view and add an entry for first name while leaving the last name field empty.  You will get a constraint violation error.