Congratulations to Carlos Antonio Alvarado Andrade and Toby Ovod-Everett. Carlos won first prize of $100 for the best solution to the February Reader Challenge, "Imposing Data Restrictions." Toby won second prize of $50. Here's a recap of the problem and the solution to the February Reader Challenge.
Problem:
Arun is a database architect who designs database schemas for products that use SQL Server 2000 and 7.0. Arun is currently working on a schema for a product that lets end users configure field names. The product's schema table contains a column that stores a field name, and this field name is displayed in the product's UI. The table can be installed under any case-insensitive database collation supported by SQL Server. The schema of the table looks like this:
create table meta_FieldNames ( fieldid int not null primary key, fieldname nvarchar(50) not null )
As part of the schema design, Arun wants to allow only a mix of upper or lowercase alphabetical characters with no numbers, international alphabet characters (e.g., accent marks, tildes) or special characters in the field names. How can he impose this restriction on the "fieldname" column of the table?
Solution:
Arun can use a CHECK constraint on the fieldname column to impose restrictions on the data that users enter. He can add the check constraint by using an ALTER TABLE statement that uses a LIKE expression to verify the value:
ALTER TABLE meta_FieldNames ADD CONSTRAINT CK_FieldName CHECK (fieldname NOT LIKE '%\[^a-z\]%')
However, although this expression will restrict upper or lowercase letters depending on the collation of the column, it won't restrict letters with special characters. For example, this CHECK constraint will let users enter an "a" with an accent mark in the column. This expression alone doesn't satisfy Arun's requirements, so he should modify the expression to include an explicit list of letters:
ALTER TABLE meta_FieldNames DROP CK_FieldName ALTER TABLE meta_FieldNames ADD CONSTRAINT CK_FieldName CHECK (fieldname NOT LIKE '%\[^abcdefghijklmnopqrstuvwxyz\]%')
By modifying the LIKE expression to include the explicit list of letters, Arun ensures the constraint's success, irrespective of the various alphabetical letters' sorting order based on the collation of the column. Finally, to prevent users from entering empty values (') into the column, Arun can modify the CHECK constraint to include a LEN check:
ALTER TABLE meta_FieldNames ADD CONSTRAINT CK_FieldName CHECK (fieldname NOT LIKE '%\[^abcdefghijklmnopqrstuvwxyz\]%' AND len(fieldname) > 0)
To validate the CHECK constraint, Arun can run the following insert statements:
INSERT INTO meta_FieldNames values (1, 'aaaaaa') INSERT INTO meta_FieldNames values (2, 'AAAAAA') INSERT INTO meta_FieldNames values (3, '12345') -- will be rejected INSERT INTO meta_FieldNames values (4, 'a1') -- will be rejected INSERT INTO meta_FieldNames values (5, 'A1') -- will be rejected INSERT INTO meta_FieldNames values (6, 'a,b,c') -- will be rejected INSERT INTO meta_FieldNames values (7, 'A,B,C') -- will be rejected INSERT INTO meta_FieldNames values (8, ') -- will be rejected
MARCH READER CHALLENGE:
Now, test your SQL Server savvy in the March Reader Challenge, "Enforcing Restricted Uniqueness on Columns" (below). Submit your solution in an email message to [email protected] by February 16. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Problem:
John is a database architect for a company that uses a sales application populated with customer data from various data sources. The customer data is stored in a SQL Server 2000 database table called Customers. The following column shows some of the columns in the table:
CREATE TABLE Customers ( CustomerId int identity(1,1) NOT NULL primary key, CompanyCode varchar(10) NULL /* ... other columns ...*/ )
The CompanyCode column isn't always present in the data that SQL Server imports into the table. In some cases, the value can be empty or NULL. John wants to enforce uniqueness on the CompanyCode column-but only for non-null values-and exclude empty values. How can John enforce uniqueness with minimal coding and without affecting the legacy applications that provide the customer data?
