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


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

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.