Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

SQL Server implements a whole family of constraints. These are used for a variety of purposes but generally are there to make sure your database integrity is maintainedboth data integrity and referential integrity. Yes, constraints can be used to enforce business rules, and I alluded to that earlier in this section. SQL Server supports the following types of constraints:

• NULL (or NOT NULL): This constraint specifies whether a column can accept a NULL value. NULL means that the data is not known. I discuss NULLs later in this section.

• CHECK: As discussed earlier, this validates data integrity by limiting what values can be applied to a column. These are defined when a table is first created or added later with the ALTER TABLE function.

• UNIQUE: These constraints ensure that a specific column is unique within the scope of a table. This prevents SQL Server from adding a new row that has the same (or NULL) column value.

• PRIMARY KEY: This constraint defines the column(s) that make a row uniquebut in this case, the primary key cannot be NULL. This means no two rows in a table with a PRIMARY KEY constraint assigned to one or more columns can be the same.

• FOREIGN KEY: This constraint indicates that a value in the designated columns must exist as a value (usually a primary key) in another table. This constraint is used to help enforce referential integrity between tables related with primary key (PK)/foreign key (FK) relationships. For example, when the Customers and Orders tables have a FOREIGN KEY constraint defined, you can't create a new row in the Orders table without referring to an existing Customers table column (usually the PK like CustID). I discussed these relationships earlier in this section. This constraint also means you might not be able to delete a row from the Customers table that has an existing row in the Orders table.

This constraint has several options of its own to dictate what should happen when T-SQL tries to delete or update a row that's tied to another table with a PK/FK relationship. These options are:

• NO ACTION: (The default.) The operation that would violate the PK/FK relational integrity.

• CASCADE: This tells SQL Server to delete all rows in the FK table that point to the deleted or changed row. This means if you use this option, when you delete a Customer, all of the rows in the Orders table for this customer are also dropped. Ah, be careful when you choose this option. It make it very easy to delete entire regions of your database.

• SET NULL: Instead of deleting the FK rows, the FK/PK column value is simply set to NULL.

• SET DEFAULT: Instead of deleting the FK rows, the FK/PK column value is set to the defined Default value.

Each of these constraints can be established using the SQL Server Management Studio IDE. While the dialog to set constraints is carefully hidden, if you open a connection to the target server and open the Object Explorer, drill into the list of Tables, choose a specific table, right-click and choose "Modify", you'll see a two-part dialog that can be used to modify the table. To access the table's Properties, right-click on the white-space next to the column definitions (at the top). This opens a dialog that supports setting the constraints, as shown in Figure 2.64.

This dialog is your gateway to the UI dialogs used to add and delete table columns; set or remove the primary key (and set the PRIMARY KEY constraint); maintain PK/FK relationships; maintain table, FullText, and XML indexes; as well as maintain Check Constraints. For example, to create the CHECK constraint defined earlier on the Discount column, you could click on "Check Constraints..." and fill in the dialog as shown in Figure 2.65. Notice that the syntax for the constraint expression is a bit different. In this case, simply refer to the column to be tested by name. In the constraint I created, I'm still validating the value to be between 0 and 99.