Configuring and Programming Integrity

If we examine the T-SQL CREATE TABLE or ALTER TABLE statement in the previous chapters or in Books Online, we can see that these statements accept several arguments for setting up built-in or system (declarative) integrity constraints using default values, check constraints, foreign keys, primary keys, unique keys, and so on.

Default Values

The default value is used in a column that may or may not forbid NULL values. For example, a column of type integer might take a 0 as the default, while a character-based column might take “unknown” as a default value-or even something like “pending.”

Note 

A default value is not really a constraint per se because it does not restrict you from entering a certain value; it only kicks in when you do not provide a value.

Default values do not just happen automatically when you insert new rows in a table. As demonstrated in the Chapter 16, you must explicitly tell SQL Server to apply the default value when performing a row insert. SQL Server can also create the row in the INSERT statement and apply all the default values for the row (in which case you would not specify columns in the INSERT statement). This is achieved using the DEFAULT VALUES clause in your INSERT statement.

The following code demonstrates the provisioning of a default value in the CREATE TABLE statement:

 CREATE TABLE Items (Amount int DEFAULT 0, Description varchar(50), Notes text NULL)

You are not limited in terms of T-SQL code as to how you can concoct a default value, as long as the default value does not violate the data type. For example, you can use system-supplied values, such as those that are returned from built-in functions, to provide a default value. The following code automatically provides information on the user who inserted the row:

 CREATE TABLE NewOrders (custID INT NOT NULL PRIMARY KEY,  custName VARCHAR(30) NOT NULL DEFAULT 'new customer',  createdBy varchar(50) DEFAULT User)

However, changing the default value is another matter altogether when you need to do things in T-SQL code, because you can’t easily reference the default name, at least not according to SQL-92 or Microsoft specifications. The CREATE TABLE statement also provides no facility for providing a custom name that would be easy to reference in a script. SQL Server, on the other hand, provides a name for the default property, but it too cannot be referenced easily from front-end applications or T-SQL scripts.

So you cannot willy-nilly change the default when the MIS walks in and asks you to do so. You have to first delete it and then recreate it. Deleting the default can be achieved using the following ALTER TABLE statement:

 ALTER TABLE [dbo].[NewOrders]   DROP CONSTRAINT [DF__defs__Items__48CFD27E]

The constraint name in the preceding code was provided automatically by SQL Server, so you can see how difficult it is to reference it. This code works, but I had to open a query window in Management Studio to look it up and then script it out to a new query window.

This is a real pain. A better method is to look up the default name in the system tables. After looking for the information in the system tables and tinkering around with QA for an hour, I arrived at the following code to delete the default programmatically:

 USE MASTER DECLARE @dfltname varchar(100), @cmd varchar(1000) SET @dfltname =   (SELECT name FROM sysobjects sysobs    JOIN sysconstraints scons ON sysobs.id = scons.constid       WHERE object_name(sysobs.parent_obj)= 'NewOrders'       AND sysobs.xtype = 'D' AND scons.colid =   (SELECT colid FROM syscolumns where ID = object_id('dbo.defs')     AND name = 'custName')) SET @cmd = 'ALTER TABLE NewOrders DROP CONSTRAINT ' + @dfltname EXEC (@cmd)

This is by no means an easy statement to comprehend, especially if you are new to T-SQL, so you might want to come back to it after you have gone through the next couple of chapters, which discuss SELECT, JOIN, aliases, EXEC, and stored procedures. In Chapter 14, I put the code in a stored procedure so that it’s two parameters away from me when I need it.

As an alternative, you can create a default object (or several objects), install a default value as the object’s property, and then bind and unbind the object to the column as needed. There are, however, a number of problems associated with default objects:

• They are not ANSI-compliant. The default objects have been dragged up through the ages from the romance between Microsoft and Sybase.

• The code to create the default object (CREATE DEFAULT) and the ensuing execution of system stored procedures sp_bindefault and sp_unbindefault is tedious.

• They are not true SQL DDL and as such are not as efficient as ANSI default constraints.

• You will be limited in the expression you use to provide a default value. As demonstrated earlier, a T-SQL ANSI default value can be derived from a sophisticated query/calculation providing a unique value each time the default in invoked. The makes the ANSI default more powerful by an order of magnitude.

• Managing defaults can drive you nuts. If you need to drop a column that has a default object fused to it, you first need to unbind the default from the column. You cannot simply delete the default either, because it might be bound to other columns.

As I am sure you are aware, you can create and manage tables from Management Studio, which essentially provides you with an interactive and visual “hookup” to integrity application for databases and tables.

You can create the default in Management Studio by opening the table in the Design Table console and entering a default value in the default value for the column selected. You can easily change the defaults in this manner. Adding or changing a default value in the console is illustrated in Figure 12–3. To add or change a default interactively, take the following steps:

1. Drill down to the database in question and expand the tree to access the table node. Expand the table node.

2. Select the table, and expand it further to expose the tree of columns. Right-click the target column and choose Modify. The properties of the column in edit mode opens in the details pane and gives you access to the column properties (see Figure 12–3).

3. Select the value and enter the value in Default Value or Binding option (under General). Close the dialog box to save the new settings.

Figure 12–3: Adding or changing a default value on a column

The SMO object model provides similar access to the default property, but you will encounter the same difficulty in accessing the default objects in code. The following points about default constraints should be taken into account:

• The default mechanism is only activated on the INSERT statement.

• You can obviously only have one default to a column; there is no way of specifying a member of a default collection.

• The default cannot be used on identity columns or on timestamp columns.

Check Constraints

The check constraint is useful because a single constraint definition can be applied to more than one column. You can also define as many check constraints as you need. For example, if you want to ensure that data for a telephone number is inserted or updated as (XXX) XXX-XXXX, you can create a check to prevent a value of any other form from being committed to the database.

The first action to take in defining checks is to list the check requirements and definitions in your integrity plan as described earlier. A check constraints list might contain the following items:

• Telephone and fax numbers must be formatted (XXX) XXX-XXXX

• Social Security numbers must be formatted XXX-XX-XXXX

• Invalid states for UPS ground are Hawaii and Alaska

You can attach a check constraint in a database diagram (see Chapter 11) or the table designer as follows:

1. Select Modify on a column as described for Figure 12–3.

2. Go to the toolbar and select the Manage Check Constraints button (the last one). Note the three buttons at the far right of the toolbox on this console: These relate to the application of indexing, integrity, and constraints. Each button represents a different dialog box.

3. The Check Constraints dialog box loads, illustrated in Figure 12–4.

   
   Figure 12–4: Adding a check constraint interactively in Management Studio

4. Click Add to create a new check constraint. Enter the check code in the constraint expression window. (You cannot check the expression syntax in this dialog box, and you will not be able to close the dialog, and thus save the check expression, if the code is incorrect. You might consider building the code in a query window first and testing it as T-SQL script, which for many architects, including me, is much slicker than fiddling in dialog boxes.) If the expression works, click the Save button on the toolbar and close the table designer.

The following code applies the preceding check constraints:

 ALTER TABLE Customers ADD CONSTRAINT phones CHECK (phone_no LIKE '(XXX) XXX-XXXX')

and

 ALTER TABLE Users ADD CONSTRAINT socials CHECK (SSN LIKE 'XXX-XX-XXXX')

and

 ALTER TABLE Orders ADD CONSTRAINT CK_states CHECK (State <> 'Hawaii')

If you were just setting out to create the preceding Orders table, you might script in the constraint at the same time. The following creates the Orders table and then applies the constraint at the table level:

 CREATE TABLE Orders     (State varchar(40) NOT NULL,      Shipper varchar(40) NOT NULL,      CONSTRAINT CK_states CHECK (State NOT IN ('Hawaii' 'Alaska')))

The T-SQL syntax is rich, and so you can code for sophisticated expressions for the preceding checks and the other constraint objects. The preceding code added another U.S. state to check for in the constraint CK_states demonstrated earlier. The code obviates the need to add a second check for Alaska to keep it out of the UPS ground column in my table. Instead I used a comma-separated list and the expression tells SQL Server that either Hawaii or Alaska are not allowed in the column. I repeated the list technique in the ALTER TABLE statement as follows:

 ALTER TABLE Orders   drop constraint CK_states go ALTER TABLE Orders ADD CONSTRAINT CK_states CHECK   (State NOT IN ('Hawaii', 'Alaska'))

The following rules governing check constraints should be kept in mind:

• They only fire on INSERT and UPDATE actions.

• They can reference more than one column in a table, in the check expression.

• They cannot be used on identity columns, timestamp columns, and unique identifier columns.

• The check expressions cannot contain subqueries.

• You can attach as many constraints as you like to a column or collection of columns.

• You do not necessarily have to include the keyword CONSTRAINT in your code, but I would do so for clarity.

• You do not need to provide a name for the constraint. Knowing SQL Server’s affinity for inventing names that look and sound like ancient Greek, however, I prefer to use a naming convention that makes it easier to read the code and document our team’s solutions.

Otherwise, enjoy them.

Tip 

Check constraints enforce domain integrity and I recommend you install them whenever necessary to keep garbage out of the database and ensure the integrity of the database. Using check constraints without regard for the client environment is a bad idea, especially in Internet applications where people connect through their Web browsers, because all manner of badly formatted strings, formats, and contradictory values will come flying at SQL Server, causing it to balk and flame the client, which causes network roundtrips and a degradation of resources. You should use client or middle-tier constraint mechanisms to avoid this, such as masked edit fields, allowable values lists, and so on wherever you can, leaving SQL Server as the last line of defense.

Foreign Keys

If you examine the T-SQL CREATE TABLE and ALTER TABLE syntax, you will see that you can create the foreign key (FK) constraints to enforce and ensure referential integrity and cascading updates when you create the table. Also note that they can be created and managed when you alter tables and their columns.

When you create the keys, remember the following rules:

• The foreign key must reference a primary key or a unique key in the partner tables.

• Foreign keys do not automatically create indexes.

• Permissions apply, so you need to ensure that users have SELECT and DRI permissions enabled on the tables (this is discussed in detail in Chapter 16, with code examples).

The following code adds a foreign key constraint to the ShipTo table in my Customers database, which I called “FK_ShipTo_CustDetails,” and the constraint references the CustDetails table:

 ALTER TABLE [dbo].[ShipTo] ADD CONSTRAINT [FK_ShipTo_CustDetails] FOREIGN KEY       ([CustID]) REFERENCES [CustDetails] ([CustID])        ON DELETE CASCADE  ON UPDATE CASCADE

In this code, when I update or delete the row in the CustDetails table, the constraint ensures the ShipTo table’s corresponding row is likewise deleted (referential integrity) or updated.

To create the constraint in Management Studio, do the following:

1. Select Modify and then click the Relationships button on the toolbar. The Relationships dialog box loads, as shown in Figure 12–5.

   
   Figure 12–5: Adding a foreign key constraint

2. Select the corresponding columns that represent the primary and foreign keys.

3. Select the options such as the cascades, deletes, and updates.

4. Change the name of the constraint, if you need to, and then close the dialog box to commit the changes to the table.

Primary and Foreign Keys

As discussed earlier, entity integrity ensures that rows in a table are unique. The tools to use to enforce uniqueness are the primary key constraints (PK), the unique key constraints (UK), and the identity property (discussed in Chapter 10). The primary and unique keys are also referenced in referential integrity constraints.

The primary key was discussed in Chapter 3 and again in Chapter 10, so I will not go over it again here.

If you examine the CREATE TABLE and ALTER TABLE syntax, you will see that you can create the primary key when you create the table and that it can be created and managed when you alter the table and its columns.

When working with the ALTER TABLE statement, you need to remember that you can only have one primary key in a table. In other words, there can only be one column that is the beneficiary of a primary key

While you can modify the primary key interactively with the graphical tools, such as Management Studio, you have to delete the key and then recreate it in T-SQL. The ALTER TABLE statement can only be used to drop or add a primary key to the table, not to alter the key itself.

Also, when adding the key, remember that the target column must have no duplicate data, nor can it accept NULL values. While NULL values are hardly duplicates, SQL Server doesn’t see it that way, because it cannot reference a unique value if the value is technically missing.

The code to add a primary key constraint to the table is as follows:

 ALTER TABLE [dbo].[CustDetails]   ADD CONSTRAINT [PK_CustDetails]   PRIMARY KEY NONCLUSTERED     ([CustID])

Note 

Chapter 10 also looks at the CREATE TABLE statement in more depth and highlights the differences between table-level definitions and column-level definitions.

To add a primary key constraint, open the Design Table dialog box and take the following steps (remember you can also do this in a database diagram as discussed in Chapter 10):

1. Select Modify and click the Manage Indexes And Keys button on the toolbar. The Indexes/Keys dialog box opens, as illustrated in Figure 12–6.

   
   Figure 12–6: Adding primary key constraints

2. Select the column name and sort order for the key

3. Select the options to be used by SQL Server on the primary key clustering.

4. Click the Close button to commit the changes to the table.

Unique keys, or unique key constraints, are very similar in function to primary keys, but the difference is that the unique key can be used to generate a nonclustered index and can live in a table that already has a primary key installed on another column. The unique key can be created in T-SQL and interactively (or the SMO object model).