Enforcing Integrity: Constraints (Declarative Data Integrity)

SQL Server uses Transact-SQL structures to enforce data integrity. You can create these structures during table creation or by altering the table definition after the creation of the table and even after data has been inserted into the table.

To enforce entity integrity, SQL Server uses PRIMARY KEY and UNIQUE constraints, UNIQUE indexes, and the IDENTITY property. UNIQUE indexes are covered in Chapter 6, "Optimizing Access to Data: Indexes."

For domain integrity, SQL Server provides system-supplied and user-defined data types, CHECK constraints, DEFAULT definitions, FOREIGN KEY constraints, NULL and NOT NULL definitions, and RULE and DEFAULT objects. Data types were covered in Chapter 2, "Elements of Transact-SQL."

To enforce referential integrity, you can use FOREIGN KEY and CHECK constraints. Using complex structures, such as stored procedures, triggers, and user-defined functions as part of constraint definitions, it is possible to enforce complex business integrity rules.

Primary Keys

To enforce entity integrity in a given table, select the fields or combination of fields that uniquely identifies every row.

Tables usually represent an entity, such as Products, and the primary key can be as simple as one single field, which contains the unique identifiers for objects of that entity. You could consider the name of a product as the unique identifier, but usually names are not unique in a table. That's why to uniquely identify every product, you introduce a unique value in the field ProductID. This avoids ambiguity when referring to one specific product.

Following a pure relational database design, you should identify which set of natural attributes uniquely identifies every object of that entity. In some cases, this set will be a single attribute although, in most cases, this set will be a collection of different attributes. In a pure relational design, you should define the PRIMARY KEY on this set of attributes. However, you can create an artificial attribute, called a surrogate key, that uniquely identifies every row, working as a simplification of the natural PRIMARY KEY.

Providing a new artificial integer column to be used as a primary key has some advantages. It is a short value—only 4 bytes—and SQL Server uses this value very efficiently on searching operations and joining tables through this field.

You can define the primary key constraint at column level, after the column definition, or at table level, as part of the table definition. Another possibility is to create the table first and add the primary key constraint later, using the ALTER TABLE statement.

You can use the code in Listing 7.1 to create a PRIMARY KEY in a single column of a table, using the CREATE TABLE statement.

Code Listing 7.1. Define a PRIMARY KEY in a Single Column

						
-- Define a PRIMARY KEY in a single column
-- using the DEFAULT constraint name

CREATE TABLE NewRegions (
RegionID int NOT NULL
PRIMARY KEY NONCLUSTERED,
RegionDescription nchar (50) NOT NULL ,
)
GO

DROP TABLE NewRegions
GO

-- Define a PRIMARY KEY in a single column
-- specifying the constraint name

CREATE TABLE NewRegions (
RegionID int NOT NULL
CONSTRAINT PK_NewRegions
PRIMARY KEY NONCLUSTERED,
RegionDescription nchar (50) NOT NULL ,
)
GO

DROP TABLE NewRegions
GO

-- Define a PRIMARY KEY in a single column
-- specifying the constraint name
-- and defining the constraint at table level

CREATE TABLE NewRegions (
RegionID int NOT NULL,
RegionDescription nchar (50) NOT NULL ,
CONSTRAINT PK_NewRegions
PRIMARY KEY NONCLUSTERED (RegionID), 
)
GO

DROP TABLE NewRegions
GO

-- Define a PRIMARY KEY in a single column
-- specifying the constraint name
-- and defining the constraint at table level
-- using the ALTER TABLE statement

CREATE TABLE NewRegions (
RegionID int NOT NULL,
RegionDescription nchar (50) NOT NULL)
GO
ALTER TABLE NewRegions
ADD CONSTRAINT PK_NewRegions
PRIMARY KEY NONCLUSTERED (RegionID)
GO

DROP TABLE NewRegions
GO

To define a primary key in a column, the column cannot accept nulls.

Because primary key values must be unique, SQL Server creates a UNIQUE index to help check whether new values already exist in the column. Without an index, as explained in Chapter 6, SQL Server would have to read every single row to determine the uniqueness of each new value. This index takes the same name as the primary key constraint, and it cannot be removed without removing the constraint.

You can provide properties for the index, such as CLUSTERED, NONCLUSTERED, or FILLFACTOR, in the constraint definition. Listing 7.2 shows how to declare the index of a PRIMARY KEY as NONCLUSTERED and with a FILLFACTOR of 70%. Index properties were detailed in Chapter 6.

Code Listing 7.2. Define a PRIMARY KEY and Select Properties for Its UNIQUE INDEX

						
-- Define a PRIMARY KEY in a single column
-- and create the index a nonclustered
-- and 70% FillFactor

CREATE TABLE NewRegions (
RegionID int NOT NULL
PRIMARY KEY NONC
LUSTERED WITH FILLFACTOR = 70,
RegionDescription nchar (50) NOT NULL ,
)
GO

DROP TABLE NewRegions
GO

It is possible to create a primary key in a group of columns. In this case, none of the columns in the primary key can accept nulls.

Listing 7.3 shows how to create a PRIMARY KEY constraint on the combination of the ProductID and the SaleID columns using two different versions: The first version creates the PRIMARY KEY directly in the CREATE TABLE statement; the second version creates the PRIMARY KEY using the ALTER TABLE statement, after the creation of the table.

Code Listing 7.3. Defining a Multicolumn PRIMARY KEY

						
-- Define a composite PRIMARY KEY in two columns
-- specifying the constraint name


CREATE TABLE ProductSales (
ProductID int NOT NULL,
SaleID int NOT NULL,
Quantity int NOT NULL,
Price money NOT NULL,
Description varchar(200) NULL,
CONSTRAINT PK_ProductSales
PRIMARY KEY NONCLUSTERED (ProductID, SaleID)
)
GO

DROP TABLE ProductSales
GO

-- Define a composite PRIMARY KEY in two columns
-- using the ALTER TABLE statement


CREATE TABLE ProductSales (
ProductID int NOT NULL,
SaleID int NOT NULL,
Quantity int NOT NULL,
Price money NOT NULL,
Description varchar(200) NULL)

ALTER TABLE ProductSales
ADD CONSTRAINT PK_ProductSales
PRIMARY KEY NONCLUSTERED (ProductID, SaleID)
GO

DROP TABLE ProductSales
GO

SQL Server can automatically provide values for a primary key defined in a single column in the following ways:

• By using the IDENTITY property for the column to specify the seed and increment values.

• By using the uniqueidentifier data type combined with the NEWID function as a DEFAULT definition to supply automatic GUID (Global Unique Identifier) values.

• By declaring a user-defined function as a DEFAULT definition, to provide unique values to the column.

• By declaring the column using a data type timestamp or rowversion. Although this is a technically correct option, it is not a recommended solution because these values change whenever the row is modified. Having a PRIMARY KEY defined in a timestamp column will make this constraint unsuitable as a reference for FOREIGN KEY constraints defined on related tables.

You can use Enterprise Manager to define a PRIMARY KEY in a table. To do it, right-click the table and select Design Table to display the Design Table form.

Figure 7.1 shows the Design Table form in which you can see the key icon on the PRIMARY KEY field. To delete the PRIMARY KEY, click the Set Primary Key icon on the toolbar.

To specify more properties about the PRIMARY KEY, you can open the Properties form by clicking the Table and Index Properties icon on the toolbar. In the Indexes/Keys tab, you can modify or delete the PRIMARY KEY definition, but you cannot create a PRIMARY KEY using this form.

Figure 7.2 shows the Properties form in which you can see how to change the FILLFACTOR or the CLUSTERED property of the index associated to the PRIMARY KEY.

UNIQUE Constraints

You can create a PRIMARY KEY to enforce uniqueness in a field or group of fields, but you can have only one PRIMARY KEY per table.

If you require enforcing uniqueness in other columns, you can create a UNIQUE constraint. For example, you can have a PRIMARY KEY defined in the EmployeeID surrogate key in the Employees table, but you want to enforce uniqueness on

• Social security number if all your employees have one

• National identification number in countries where this is the standard identification

• Passport number on overseas projects

• Driver's license number for your Drivers table.

Full Name is not usually a good candidate for a UNIQUE constraint, because you can have many employees with the same name.

ProductName can be a good candidate, unless you have multiple products with the same name and different size, color, or any other attribute. In this case, you either provide a name with the full description, including all these attributes, or consider the combination of name and attributes as unique in your table.

A UNIQUE constraint is similar to a PRIMARY KEY, but you can have more than one UNIQUE constraint per table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case, the index defaults to NONCLUSTERED, because you can have more than one UNIQUE constraint but only one clustered index.

Contrary to PRIMARY KEY constraints, UNIQUE constraints can accept NULL values, but just one. If the constraint is defined in a combination of fields, every field can accept NULL and have some NULL values on them, as long as the combination of values is unique.

Suppose you declared a UNIQUE constraint in the combination of the fields (HouseNumber, HouseName, Apartment, Address, City). In this case, you can have many rows where any of these fields are NULL, but only one row can have all these fields NULL. In this example, the table can have different combinations of NULL entries:

• Only one row with NULL in all these fields.

• Many rows with NULL value in HouseNumber, HouseName, Apartment, Address or City, as long as the other fields are not NULL at the same time.

• Many rows with NOT NULL values in these fields.

If you want to enforce uniqueness in a column that accepts many NULL values, a UNIQUE constraint cannot help you. In this case, you should use a trigger or a CHECK constraint with a user-defined function. You can see examples of both strategies in Chapters 9 and 10.

Trying to create a UNIQUE constraint in a row or combination of rows with nonunique values will give you an error message.

You can specify options for the UNIQUE index, in the same way as in the PRIMARY KEY definition. For more index options, you can create a UNIQUE index instead of a UNIQUE constraint.

You can use the code of Listing 7.4 to create a UNIQUE constraint in a single column of a table, using the CREATE TABLE and the ALTER TABLE statements, using different syntax options.

Code Listing 7.4. Create UNIQUE Constraints

						
-- Define a UNIQUE constraint in a single column
-- using the default constraint name

CREATE TABLE NewRegions (
RegionID int NOT NULL
UNIQUE NONCLUSTERED,
RegionDescription nchar (50) NOT NULL ,
)
GO

DROP TABLE NewRegions
GO


-- Define a UNIQUE constraint in a single column
-- specifying the constraint name

CREATE TABLE NewRegions (
RegionID int NOT NULL
CONSTRAINT UC_NewRegions
UNIQUE NONCLUSTERED,
RegionDescription nchar (50) NOT NULL ,
)
GO
DROP TABLE NewRegions
GO

-- Define a UNIQUE constraint in a single column
-- specifying the constraint name
-- and defining the constraint at table level

CREATE TABLE NewRegions (
RegionID int NOT NULL,
RegionDescription nchar (50) NOT NULL ,
CONSTRAINT UC_NewRegions
UNIQUE NONCLUSTERED (RegionID),
)
GO

DROP TABLE NewRegions
GO

-- Define a UNIQUE constraint in a single column
-- specifying the constraint name
-- and defining the constraint at table level
-- using the ALTER TABLE statement

CREATE TABLE NewRegions (
RegionID int NOT NULL,
RegionDescription nchar (50) NOT NULL)

ALTER TABLE NewRegions
ADD CONSTRAINT UC_NewRegions
UNIQUE NONCLUSTERED (RegionID)
GO

DROP TABLE NewRegions
GO

As detailed earlier in this chapter for PRIMARY KEYs, you can provide properties for the index, such as CLUSTERED, NONCLUSTERED, or FILLFACTOR, in the constraint definition. Listing 7.5 shows how to declare the index of a UNIQUE constraint as nonclustered and with a FILLFACTOR of 70%. Index properties were detailed in Chapter 6.

Code Listing 7.5. Create a UNIQUE Constraint and Define Properties of Its UNIQUE INDEX

						
-- Define a UNIQUE constraint in a single column
-- and create the index a nonclustered
-- and 70% FillFactor
CREATE TABLE NewRegions (
RegionID int NOT NULL
UNIQUE NONCLUSTERED WITH FILLFACTOR = 70,
RegionDescription nchar (50) NOT NULL ,
)
GO

DROP TABLE NewRegions
GO

Listing 7.6 shows how to create a multicolumn UNIQUE constraint. A composite UNIQUE constraint is limited to 16 columns and 900 bytes for the key size.

Code Listing 7.6. Creating a Multicolumn UNIQUE Constraint

						
-- Define a composite UNIQUE in two columns
-- specifying the constraint name


CREATE TABLE ProductSales (
ProductID int NOT NULL,
SaleID int NOT NULL,
Quantity int NOT NULL,
Price money NOT NULL,
Description varchar(200) NULL,
CONSTRAINT UC_ProductSales
UNIQUE NONCLUSTERED (ProductID, SaleID)
)
GO

DROP TABLE ProductSales
GO

-- Define a composite UNIQUE constraint in two columns
-- using the ALTER TABLE statement


CREATE TABLE ProductSales (
ProductID int NOT NULL,
SaleID int NOT NULL,
Quantity int NOT NULL,
Price money NOT NULL,
Description varchar(200) NULL)
ALTER TABLE ProductSales
ADD CONSTRAINT UC_ProductSales
UNIQUE NONCLUSTERED (ProductID, SaleID)
GO

DROP TABLE ProductSales
GO

You can provide default values for a UNIQUE field in the same way as for the PRIMARY KEY:

• Using the IDENTITY property for the column, specifying the seed and increment values.

• Using the uniqueidentifier data type combined with the NEWID function as a DEFAULT constraint to supply automatic GUID (Global Unique Identifier) values.

• Declare a user-defined function as a DEFAULT constraint, to provide unique values to the column.

• Declare the column using a data type timestamp or rowversion. However, this option is useless because timestamp values are unique, regardless of the existence of a UNIQUE constraint, and they change whenever the row changes.

You can use Enterprise Manager to define a UNIQUE constraint in a table. To do it, right-click the table and select Design Table to display the Design Table form, and then click the Table and Index Properties icon on the toolbar. In the Indexes/Keys tab, you can create, modify, or delete UNIQUE constraints.

In the Properties form, you have the choice to create a UNIQUE constraint or a UNIQUE index. Use UNIQUE index if you want to provide extra functionality as Ignore Duplicate Key or Do Not Automatically Recompute Statistics.

Figure 7.3 shows the Properties form in which you can see how to define Properties for a UNIQUE constraint. Figure 7.4 shows a similar form to specify properties for the UNIQUE index associated to the UNIQUE constraint.

CHECK Constraints

A CHECK constraint defines a condition for one or more columns in a table on INSERT and UPDATE operations. This condition can be defined by any expression that returns TRUE or FALSE. If the condition returns TRUE, the operation continues, but if the condition returns FALSE, the operation is automatically rolled back.

It is possible to have many CHECK constraints per table, in which case they will be checked in creation order. If one CHECK constraint fails, the operation is rolled back and no more CHECK constraints are tested. In these situations, the client receives a single error message with information about the CHECK constraint that failed.

To define a CHECK constraint, you can use any expression as long as it references only columns in the same table. A CHECK constraint cannot reference other rows in the same table or other tables, but it is valid to use a user-defined function as part of the CHECK definition, and the user-defined function can use data from other tables, databases, or servers. In Chapter 10, we'll discuss how to use user-defined functions in CHECK constraints.

You can use a CHECK constraint to verify

• That the value has a valid format. For example, the Postcode should be a five-digit number.

• That the value is in a specific range of valid data. For example, the UnitPrice should be between 1 and 1,000, or greater than 0.

• That the value is not equal to any specific reserved value. For example, the name of a new product shouldn't be an empty string ('').

• The result of a function applied to this value. For example, PaymentDue should be not later than 90 days from the SaleDate.

You can define single column CHECK constraints during the creation of a table. Listing 7.7 shows how to create a CHECK constraint on the NewEmployees table to ensure that the PostCode column accepts only strings with five digits. The second example in Listing 7.7 creates a CHECK constraint that enforces positive values for the UnitPrice column in the Products table.

Code Listing 7.7. Create a CHECK Constraint with the CREATE TABLE Statement

						
-- Define a CHECK constraint in a single column
-- using the default constraint name

CREATE TABLE NewEmployees (
EmployeeID int NOT NULL,
EmployeeName varchar(50) NOT NULL,
PostCode char(5) NOT NULL
CHECK (PostCode LIKE '[0-9][0-9][0-9][0-9][0-9]')
)
GO

DROP TABLE NewEmployees
GO
-- Define a CHECK constraint in a single column
-- specifying the constraint name

CREATE TABLE NewProducts (
ProductID int NOT NULL,
ProductName varchar(50) NOT NULL,
UnitPrice money NOT NULL
CONSTRAINT CC_Prod_UnitPrice
CHECK (UnitPrice > 0)
)
GO

DROP TABLE NewProducts
GO

You can create a CHECK constraint at table level in the CREATE TABLE statement. This is the only way to define it if the CHECK constraint references more than one column. The expression can contain any number of subexpressions using logical operators as long as the total expression evaluates to TRUE or FALSE.

Listing 7.8 contains three examples of CHECK constraints:

• The CC_Prod_Name CHECK constraint forces the ProductName column to accept only nonempty strings.

• The CC_Order_DueDate CHECK constraint checks that the time between DueDate and SaleDate is less than or equal to 90 days.

• The third example creates the CC_Order_DueDate CHECK constraint to check three conditions simultaneously:

  • The DueDate should be on or prior to 90 days after the SaleDate.

  • The SaleDate cannot be a future date.

  • The ShipmentMethod can have only three possible values: air ('A'), land ('L'), or sea ('S').

Code Listing 7.8. Create Multicolumn CHECK Constraints with the CREATE TABLE Statement

-- Define a CHECK constraint in a single column
-- specifying the constraint name
-- and defining the constraint at table level

CREATE TABLE NewProducts (
ProductID int NOT NULL,
ProductName varchar(50) NOT NULL,
UnitPrice money NOT NULL ,
CONSTRAINT CC_Prod_Name
CHECK (ProductName <> '')
)
GO

DROP TABLE NewProducts
GO

-- Define a CHECK constraint in a single column
-- specifying the constraint name
-- and defining the constraint at table level
-- as an expression

CREATE TABLE NewOrders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
SaleDate smalldatetime NOT NULL ,
DueDate smalldatetime NOT NULL,
CONSTRAINT CC_Order_DueDate
CHECK (DATEDIFF(day, SaleDate, DueDate) <= 90) 
)
GO

DROP TABLE NewOrders
GO

-- Define a CHECK constraint in a single column
-- specifying the constraint name
-- and defining the constraint at table level
-- as a multiple expressions linked by
-- logical operators

CREATE TABLE NewOrders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
SaleDate smalldatetime NOT NULL ,
DueDate smalldatetime NOT NULL,
ShipmentMethod char(1) NOT NULL,
CONSTRAINT CC_Order_DueDate
CHECK
((DATEDIFF(day, SaleDate, DueDate) <= 90)
AND
(DATEDIFF(day, CURRENT_TIMESTAMP, SaleDate) <= 0)
AND (ShipmentMethod IN ('A', 'L', 'S'))
)
)
GO

DROP TABLE NewOrders
GO

It is possible to create CHECK constraints for existing tables using the ALTER TABLE statement, as in Listing 7.9. In this case, you can specify whether it is necessary to check existing data.

The first example in Listing 7.9 creates three CHECK constraints on the NewOrders table: one CHECK constraint for every condition used in the last example from Listing 7.8.

The second example in Listing 7.9 creates the same CHECK constraints as in the first example, but in this case it specifies not to check existing data for the first and third CHECK constraints.

Code Listing 7.9. Create CHECK Constraints with the ALTER TABLE Statement

-- Define multiple CHECK constraint
-- in existing tables specifying
-- the constraint name and defining
-- the constraint at table level
-- using the ALTER TABLE statement
-- checking existing data

CREATE TABLE NewOrders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
SaleDate smalldatetime NOT NULL ,
DueDate smalldatetime NOT NULL,
ShipmentMethod char(1) NOT NULL)

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_DueDate
CHECK (DATEDIFF(day, SaleDate, DueDate) <= 90)

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_SaleDate
CHECK (DATEDIFF(day, CURRENT_TIMESTAMP, SaleDate) <= 0)
ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_Shipment
CHECK (ShipmentMethod IN ('A', 'L', 'S')) 

GO

DROP TABLE NewOrders
GO

-- Define multiple CHECK constraint
-- in existing tables specifying
-- the constraint name and defining
-- the constraint at table level
-- using the ALTER TABLE statement
-- checking existing data
-- only for one of the constraints

CREATE TABLE NewOrders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
SaleDate smalldatetime NOT NULL ,
DueDate smalldatetime NOT NULL,
ShipmentMethod char(1) NOT NULL)

ALTER TABLE NewOrders
WITH NOCHECK
ADD CONSTRAINT CC_Order_DueDate
CHECK (DATEDIFF(day, SaleDate, DueDate) <= 90)

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_SaleDate
CHECK (DATEDIFF(day, CURRENT_TIMESTAMP, 

ALTER TABLE NewOrders
WITH NOCHECK
ADD CONSTRAINT CC_Order_Shipment
CHECK (ShipmentMethod IN ('A', 'L', 'S'))

GO

DROP TABLE NewOrders
GO

To modify a CHECK constraint, you must drop the constraint and re-create it or use Enterprise Manager to do it. To modify a CHECK constraint using Enterprise Manager, right-click the table and select Design Table to display the Design Table form. Click the Table and Index Properties icon on the toolbar to display the Properties form, and select the Check Constraints tab. Figure 7.5 shows the Check Constraints tab of the Properties form. Using this form, you can

• Change the name of the CHECK constraint.

• Change the expression of the constraint.

• Specify whether you want to check existing data.

• Select whether you want to enforce this constraint when receiving data from replication (on by default).

• Enable the constraint for INSERT and UPDATE statements (on by default).

Checking constraints produces some overhead. You can disable a CHECK constraint for INSERT and UPDATE operations if the data to insert is checked already with the same conditions. To disable a constraint for INSERT and UPDATE operations, you can use the ALTER TABLE statement.

Listing 7.10 shows how to disable and reenable a CHECK constraint.

Code Listing 7.10. Use the ALTER TABLE Statement to Disable and Reenable Constraints

						
-- Create a CHECK constraint
-- in existing tables specifying
-- the constraint name and defining
-- the constraint at table level
-- using the ALTER TABLE statement
-- checking existing data


-- Create table

CREATE TABLE NewOrders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
SaleDate smalldatetime NOT NULL ,
DueDate smalldatetime NOT NULL,
ShipmentMethod char(1) NOT NULL)

-- Create constraint

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_DueDate
CHECK (DATEDIFF(day, SaleDate, DueDate) <= 90)

-- Disable constraint

ALTER TABLE NewOrders
NOCHECK CONSTRAINT CC_Order_DueDate

-- Reenable constraint
ALTER TABLE NewOrders
CHECK CONSTRAINT CC_Order_DueDate
GO

DROP TABLE NewOrders
GO

Replicated data has been checked already in the Publisher. Checking the same data again in the subscriber is usually unnecessary. You can create a CHECK constraint that is disabled for replicated data specifying NOT FOR REPLICATION after the CHECK keyword.

Listing 7.11 shows how to use the NOT FOR REPLICATION option in the CREATE TABLE and ALTER TABLE statements.

Code Listing 7.11. Using the NOT FOR REPLICATION Option to Avoid Checking Replicated Data

						
-- Define a CHECK constraint in a single column
-- using the default constraint name
-- specifying NOT FOR REPLICATION

CREATE TABLE NewEmployees (
EmployeeID int NOT NULL,
EmployeeName varchar(50) NOT NULL,
PostCode char(5) NOT NULL
CHECK NOT FOR REPLICATION (PostCode LIKE '[0-9][0-9][0-9][0-9][0-9]')
)
GO

DROP TABLE NewEmployees
GO

-- Define multiple CHECK constraint
-- in existing tables specifying
-- the constraint name and defining
-- the constraint at table level
-- using the ALTER TABLE statement
-- checking existing data
-- specifying NOT FOR REPLICATION

CREATE TABLE NewOrders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
SaleDate smalldatetime NOT NULL ,
DueDate smalldatetime NOT NULL,
ShipmentMethod char(1) NOT NULL) 

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_DueDate
CHECK NOT FOR REPLICATION
(DATEDIFF(day, SaleDate, DueDate) <= 90)

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_SaleDate
CHECK NOT FOR REPLICATION
(DATEDIFF(day, CURRENT_TIMESTAMP, SaleDate) <= 0)

ALTER TABLE NewOrders
ADD CONSTRAINT CC_Order_Shipment
CHECK NOT FOR REPLICATION
(ShipmentMethod IN ('A', 'L', 'S'))

GO

DROP TABLE NewOrders
GO

You can remove a CHECK constraint by using the ALTER TABLE statement. Removing a table removes constraints associated with the table.

Use the example in Listing 7.12 to create the NewProducts table, including the CC_Prod_UnitPrice CHECK constraint, and drop the constraint using the ALTER TABLE statement.

Code Listing 7.12. Use the ALTER TABLE Statement to Remove CHECK Constraints

-- Define a CHECK constraint in a single column
-- specifying the constraint name

CREATE TABLE NewProducts (
ProductID int NOT NULL,
ProductName varchar(50) NOT NULL,
UnitPrice money NOT NULL
CONSTRAINT CC_Prod_UnitPrice
CHECK (UnitPrice > 0)
)
GO

-- Drop the constraint
-- Specifying its name

ALTER TABLE NewProducts
DROP CONSTRAINT CC_Prod_UnitPrice
GO

DROP TABLE NewProducts
GO

DEFAULT Definitions

You can define a DEFAULT definition for columns to avoid repetitive data entry. If a column has a DEFAULT definition, this value will be supplied if you don't provide a specific value for the column in the INSERT statement. DEFAULT definitions are applied only in INSERT operations.

You can provide as a DEFAULT definition any expression that evaluates to a single scalar value with a data type compatible with the data type of the column in which the DEFAULT definition is defined. This expression can be

• A constant value

• Any system scalar function

• The result of a scalar user-defined function

• Any scalar expression made from any combination of the previous points, including mathematical expressions

You can create a DEFAULT definition for a new column using the CREATE TABLE or ALTER TABLE statements, defining the DEFAULT constraint at column level. To force the use of the default value, you can omit the column, provide the DEFAULT keyword, or use the DEFAULT VALUES for the INSERT statement, as detailed in Listing 7.13.

Code Listing 7.13. Create DEFAULT Definitions Using the CREATE TABLE Statement

						
-- Create the table NewCustomers
-- providing the value 'London'
-- as a column DEFAULT definition
-- for the City column,
-- a named DEFAULT constraint
-- for the CustomerName column,
-- The current date and time
-- for the CreaDate column
-- and the login name
-- for the CreaUser column


CREATE TABLE NewCustomers(
CustomerID int NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
CustomerName varchar(30) NOT NULL
CONSTRAINT Def_CustName
DEFAULT 'To be entered',
City varchar(30)
DEFAULT 'London',
CreaDate smalldatetime
DEFAULT Getdate(),
CreaUser nvarchar(128)
DEFAULT System_User)
GO

-- Insert data into the NewCustomers table
-- Providing values for CustomerName
-- and City fields

INSERT NewCustomers (CustomerName, City)
VALUES ('MyComp corp.', 'New York') 

-- Insert data into the NewCustomers table
-- Omitting to enter the City field

INSERT NewCustomers (CustomerName)
VALUES ('ACME Inc.')
SELECT *
FROM NewCustomers

-- Insert data into the NewCustomers table
-- Providing the default value
-- for the City field

INSERT NewCustomers (CustomerName, City)
VALUES ('NewDotCompany Ltd.', DEFAULT)

SELECT *
FROM NewCustomers

-- Insert data into the NewCustomers table
-- Providing the default value
-- for every nonnull field

INSERT NewCustomers
DEFAULT VALUES

SELECT *
FROM NewCustomers

-- Drop the test table

DROP TABLE NewCustomers

CustomerID  CustomerName         City      CreaDate               CreaUser
----------- -------------------- --------- ---------------------- --------
1           MyComp corp.         New York  2000-11-11 17:35:00    sa
2           ACME Inc.            Lon+++don    2000-11-11 17:35:00    sa

CustomerID  CustomerName         City      CreaDate               CreaUser
----------- -------------------- --------- ---------------------- --------
1           MyComp corp.         New York  2000-11-11 17:35:00    sa
2           ACME Inc.            London    2000-11-11 17:35:00    sa
3           NewDotCompany Ltd.   London    2000-11-11 17:35:00    sa

CustomerID  CustomerName         City      CreaDate               CreaUser
----------- -------------------- --------- ---------------------- --------
1           MyComp corp.         New York  2000-11-11 17:35:00    sa
2           ACME Inc.            London    2000-11-11 17:35:00    sa
3           NewDotCompany Ltd.   London    2000-11-11 17:35:00    sa
4           To be entered        London    2000-11-11 17:35:00    sa

You can add a DEFAULT definition to an existing column using the ALTER TABLE statement, as in Listing 7.14.

Code Listing 7.14. Create new DEFAULT Properties for New and Existing Columns with the ALTER TABLE Statement

						
-- Create the table NewCustomers
-- providing the value 'London'
-- as a column DEFAULT definition
-- for the City column,
-- and a named DEFAULT constraint
-- for the CustomerName column

CREATE TABLE NewCustomers(
CustomerID int NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
CustomerName varchar(30) NOT NULL
CONSTRAINT Def_CustName
DEFAULT 'To be entered',
City varchar(30)
DEFAULT 'London',
CreaDate smalldatetime)
GO

-- Use ALTER TABLE
-- to add a new column
-- with a DEFAULT definition

ALTER TABLE NewCustomers
ADD CreaUser nvarchar(128)
DEFAULT SYSTEM_USER

-- Use ALTER TABLE
-- to add a DEFAULT definition
-- to an existing column

ALTER TABLE NewCustomers
ADD CONSTRAINT Def_Cust_CreaDate
DEFAULT CURRENT_TIMESTAMP
FOR CreaDate
GO

-- Drop the test table

DROP TABLE NewCustomers

When you add a new column with a DEFAULT definition, the column gets the DEFAULT value automatically for existing rows if

• The column does not accept NULL

• The column accepts NULL but you specify WITH VALUES after the DEFAULT definition

Listing 7.15 shows how to add a column with a DEFAULT definition to a table with existing data, specifying the WITH VALUES option.

Code Listing 7.15. Use the WITH VALUES Option to Provide a Default Value to a New Column on Existing Rows

						
-- Create the table NewCustomers
-- providing the value 'London'
-- as a column DEFAULT definition
-- for the City column,
-- and a named DEFAULT constraint
-- for the CustomerName column

CREATE TABLE NewCustomers(
CustomerID int NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
CustomerName varchar(30) NOT NULL
CONSTRAINT Def_CustName
DEFAULT 'To be entered',
City varchar(30) 
DEFAULT 'London',
CreaDate smalldatetime
DEFAULT CURRENT_TIMESTAMP)
GO

-- Insert some data

INSERT NewCustomers (CustomerName, City)
VALUES ('MyComp corp.', 'New York')
INSERT NewCustomers (CustomerName)
VALUES ('ACME Inc.')

INSERT NewCustomers (CustomerName, City) 
VALUES ('NewDotCompany Ltd.', DEFAULT)

INSERT NewCustomers
DEFAULT VALUES

SELECT *
FROM NewCustomers
GO

-- Use ALTER TABLE
-- to add a new column
-- with a DEFAULT definition
-- filling this new column
-- in existing rows

ALTER TABLE NewCustomers
ADD CreditLimit money
DEFAULT 1000.0
WITH VALUES

SELECT *
FROM NewCustomers

GO

-- Drop the test table
DROP TABLE NewCustomers

CustomerID  CustomerName         City       CreaDate
----------- -------------------- ---------- -------------------
1           MyComp corp.         New York   2000-11-11 17:35:00
2           ACME Inc.            London     2000-11-11 17:35:00 
3           NewDotCompany Ltd.   London     2000-11-11 17:35:00
4           To be entered        London     2000-11-11 17:35:00
CustomerID  CustomerName         City       CreaDate              CreditLimit
----------- -------------------- ---------- --------------------- -----------
1           MyComp corp.         New York   2000-11-11 17:58:00   1000.0000
2           ACME Inc.            London     2000-11-11 17:58:00   1000.0000
3           NewDotCompany Ltd.   London     2000-11-11 17:58:00   1000.0000
4           To be entered        London     2000-11-11 17:58:00   1000.0000

Foreign Keys

If you have two related tables—such as TableA and TableB—their relationship can be any of these three types:

• One to one—Figure 7.6 shows the relationship between the Customers table and the PublicCustomers table (this table does not exist in the Northwind database). Every row in the PublicCustomers is related to a single row in the Customers table, but not every row in the Customers table has a related row in the PublicCustomers table. As in this example, you can use one-to-one relationships to expand a table creating a subtable to store information related only to some specific rows.

  Figure 7.6. A one-to-one relationship.

  

• One to many—Figure 7.7 shows the relationship between the Products and [Order Details] tables. For every product, you can have none, one, or many related rows in the [Order Details] table. And every row in the [Order Details] table is related to only one row in the Products table.

  Figure 7.7. A one-to-many relationship.

  

• Many to many—Every employee can work in different territories, and in every territory you can have many employees. Figure 7.8 shows the relationships between the Employees and the Territories tables. As you can see in the diagram, the problem is solved by an intersection table, EmployeesTerritories, that contains the keys to connect to both tables.

  Figure 7.8. A many-to-many relationship.

  

To establish a relationship from one table, which can be called the "children" table, to another table, which can be called the "parent" table, you must create in the children table a FOREIGN KEY constraint.

When you define a FOREIGN KEY in the children table, this constraint enforces some rules in both parent and children tables:

• In the parent table, you cannot modify the related field on any row that has any related rows in the children table. For example, you cannot change the product code, or sales in this product will be invalid.

• You cannot delete a row in the parent table that has related rows in the children table. For example, you cannot delete a product with sales, or the sales about this product will be orphans.

• You cannot insert new rows in the children table where the related field contains nonexisting values in the related field on the parent table. For example, you cannot create a sale for a nonexisting product.

• You cannot modify the related field in the children table to a new value that does not exist in the parent table. For example, you cannot change the product code in a sale for a nonexisting product code.

After the FOREIGN KEY is created, SQL Server will check every statement involving the related tables to prevent any violation of the relationship. In that case, the statement will be rejected and the data will not be modified.

You can declare a FOREIGN KEY constraint either using the CREATE TABLE or the ALTER TABLE statements. Listing 7.16 shows a complete example.

Code Listing 7.16. Create FOREIGN KEY Constraints with the CREATE TABLE or ALTER TABLE Statement

						
-- Create Customers and Orders tables

CREATE TABLE Customers(
CustomerID int
PRIMARY KEY,
CustomerName varchar(20) NOT NULL)

CREATE TABLE Orders(
OrderID int
IDENTITY(1,1)
PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate smalldatetime NOT NULL
DEFAULT CURRENT_TIMESTAMP)
GO

-- Create the FOREIGN KEY constraint

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) 
GO

/*

-- Or you could define the Orders
-- table with References
-- without using the ALTER TABLE
-- statement to define the FOREIGN KEY
CREATE TABLE Orders(
OrderID int
IDENTITY(1,1)
PRIMARY KEY,
CustomerID int NOT NULL
REFERENCES Customers(CustomerID),
OrderDate smalldatetime NOT NULL
DEFAULT CURRENT_TIMESTAMP)
--GO

*/


-- Insert some Customers

INSERT Customers
VALUES (1, 'MyComp corp.')

INSERT Customers
VALUES (2, 'ACME Inc.')

INSERT Customers
VALUES (3, 'NewDotCompany Ltd.')

-- Insert some Orders
-- with the default Date

INSERT Orders (CustomerID)
VALUES (1)

INSERT Orders (CustomerID)
VALUES (2) 

INSERT Orders (CustomerID)
VALUES (3)

INSERT Orders (CustomerID)
VALUES (3)

-- Try to update customer 3 PRIMARY KEY
-- to a different value

PRINT CHAR(10) + 'Trying to update a customer PK'+ CHAR(10)

UPDATE Customers
SET CustomerID = 30
WHERE CustomerID = 3

-- Try to insert a new order for a
-- nonexisting customer

PRINT CHAR(10) + 'Trying to insert an orphan Order'+ CHAR(10) 

INSERT Orders (CustomerID)
VALUES (10)

GO

DROP TABLE Orders

DROP TABLE Customers
Trying to update a customer PK

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders'. The conflict
 occurred in database 'ByExample', table 'Orders', column 'CustomerID'.
The statement has been terminated.

Trying to insert an orphan Order

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Orders'. The conflict
 occurred in database 'ByExample', table 'Customers', column 'CustomerID'. 
The statement has been terminated. 

You can use Enterprise Manager to create FOREIGN KEY constraints. The easiest way is by using the Diagram tool. Figure 7.9 shows a diagram for the Northwind database, including the relationships between tables.

To create a FOREIGN KEY in the Diagram tool, you can just drag a column from the children table and drop it on the parent table.

Figure 7.10 shows how to create and modify FOREIGN KEY constraints using the Properties form of the children table.

You can prevent checking existing data when you create the FOREIGN KEY constraint by using the WITH NOCHECK option.

To modify a FOREIGN KEY constraint, you must drop the constraint and re-create it.

To prevent the FOREIGN KEY constraint from checking replicated data, use the NOT FOR REPLICATION predicate.

FOREIGN KEY constraints are dropped automatically when the table is dropped. However, to drop a constraint you can use the ALTER TABLE... DROP CONSTRAINT statement, specifying the name of the constraint to drop.

Cascading Operations: Cascaded Declarative Referential Integrity

The ANSI standard establishes four modes to solve changes that could break referential integrity definitions:

• RESTRICT or NO ACTION—Trying to delete or modify a row that is linked to some rows in a children table produces an error message and the operation is rolled back. This is the default for SQL Server 2000.

• CASCADE—Modifications to a UNIQUE field are cascaded to every FOREIGN KEY that references the field. Deleting a row forces a deletion of every related row where the FOREIGN KEY references the deleted row. This mode is optional in SQL Server 2000.

• SET NULL—When the referential integrity is broken for some FOREIGN KEY values, because of an update or delete operation in the parent table, those values are set to NULL. This mode is not implemented in SQL Server 2000.

• SET DEFAULT—If the referential integrity is broken for some FOREIGN KEY values due to an update or delete operation in the parent table, the FOREIGN KEY values are set to a default value. This mode is not implemented in SQL Server 2000.

The standard mode of FOREIGN KEY constraints has been covered in a previous section. In the following sections, you are going to see how to implement cascade operations in SQL Server 2000.

Nested cascade operations interact with nested triggers in the following way:

Cascading Deletes

To define a FOREIGN KEY constraint as a cascaded DELETE action, you must use the ON DELETE CASCADE in the REFERENCES clause of the FOREIGN KEY definition on the CREATE TABLE or ALTER TABLE statements.

In Listing 7.17, you create the Customers table and the Orders table. You define a FOREIGN KEY constraint between these two tables with the ON DELETE CASCADE option. Customer 2 has three related rows in the Orders table. The operation that deleted customer 2 from the Customers table forces the deletion of the three related rows in the Orders table.

Code Listing 7.17. Cascade Deletion of Rows in Related Tables with FOREIGN KEY Constraints Defined As ON DELETE CASCADE

							
-- Create Customers and Orders tables

CREATE TABLE Customers(
CustomerID int
PRIMARY KEY,
CustomerName varchar(20) NOT NULL)

CREATE TABLE Orders(
OrderID int
IDENTITY(1,1)
PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate smalldatetime NOT NULL
DEFAULT CURRENT_TIMESTAMP)
GO

-- Create the FOREIGN KEY constraint
-- with CASCADE

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) 
ON DELETE CASCADE

GO

-- Insert some Customers
INSERT Customers
VALUES (1, 'MyComp corp.')

INSERT Customers
VALUES (2, 'ACME Inc.')

INSERT Customers
VALUES (3, 'NewDotCompany Ltd.')

-- Insert some Orders
-- with the default Date

INSERT Orders (CustomerID)
VALUES (1)

INSERT Orders (CustomerID)
VALUES (1)

INSERT Orders (CustomerID)
VALUES (2)

INSERT Orders (CustomerID)
VALUES (2)

INSERT Orders (CustomerID)
VALUES (2)

INSERT Orders (CustomerID)
VALUES (3) 

INSERT Orders (CustomerID)
VALUES (3)

-- Show the data

PRINT CHAR(10) + 'Original Customers table'+ CHAR(10)

SELECT *
FROM Customers

PRINT CHAR(10) + 'Original Orders table'+ CHAR(10) 

SELECT *
FROM Orders

GO
-- Delete Customer 2

DELETE Customers
WHERE CustomerID = 2

PRINT CHAR(10) + 'Customers table after delete Customer 2'+ CHAR(10)

SELECT *
FROM Customers

PRINT CHAR(10) + 'Orders table after delete Customer 2'+ CHAR(10)

SELECT *
FROM Orders

GO

DROP TABLE Orders

DROP TABLE Customers
Original Customers table

CustomerID  CustomerName
----------- --------------------
1           MyComp corp.
2           ACME Inc.
3           NewDotCompany Ltd.


Original Orders table

OrderID     CustomerID  OrderDate
----------- ----------- ------------------------------
1           1           2000-11-12 23:55:00
2           1           2000-11-12 23:55:00
3           2           2000-11-12 23:55:00
4           2           2000-11-12 23:55:00
5           2           2000-11-12 23:55:00
6           3           2000-11-12 23:55:00
7           3           2000-11-12 23:55:00


Customers table after delete Customer 2

CustomerID  CustomerName
----------- --------------------
1           MyComp corp.
3           NewDotCompany Ltd.


Orders table after delete Customer 2

OrderID     CustomerID  OrderDate
----------- ----------- ------------------------------
1           1           2000-11-12 23:55:00
2           1           2000-11-12 23:55:00
6           3           2000-11-12 23:55:00
7           3           2000-11-12 23:55:00

Cascading Updates

To define a FOREIGN KEY constraint as a cascaded UPDATE action, you must use the ON UPDATE CASCADE in the REFERENCES clause of the FOREIGN KEY definition on the CREATE TABLE or ALTER TABLE statements.

Listing 7.18 is based in the same example as in Listing 7.17. You create the Customers table and the Orders table. You define a FOREIGN KEY constraint between these two tables with the ON UPDATE CASCADE option. You want to change the ID of customer 3 to 30. Customer 3 has two related rows in the Orders table. The UPDATE operation changes the CustomerID from 3 to 30 in both tables automatically.

Code Listing 7.18. Cascade Changes on Primary Keys to Related Foreign Keys with FOREIGN KEY Constraints Defined As ON UPDATE CASCADE

							
-- Create Customers and Orders tables

CREATE TABLE Customers(
CustomerID int
PRIMARY KEY,
CustomerName varchar(20) NOT NULL)

CREATE TABLE Orders(
OrderID int
IDENTITY(1,1)
PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate smalldatetime NOT NULL
DEFAULT CURRENT_TIMESTAMP)
GO
-- Create the FOREIGN KEY constraint
-- with CASCADE

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)
ON DELETE CASCADE -- This is optional
ON UPDATE CASCADE
GO

-- Insert some Customers

INSERT Customers
VALUES (1, 'MyComp corp.')

INSERT Customers
VALUES (2, 'ACME Inc.')

INSERT Customers
VALUES (3, 'NewDotCompany Ltd.') 

-- Insert some Orders
-- with the default Date

INSERT Orders (CustomerID)
VALUES (1)

INSERT Orders (CustomerID)
VALUES (1)

INSERT Orders (CustomerID)
VALUES (2)

INSERT Orders (CustomerID)
VALUES (2)

INSERT Orders (CustomerID)
VALUES (2)

INSERT Orders (CustomerID)
VALUES (3)

INSERT Orders (CustomerID)
VALUES (3)
-- Show the data

PRINT CHAR(10) + 'Original Customers table'+ CHAR(10)

SELECT *
FROM Customers

PRINT CHAR(10) + 'Original Orders table'+ CHAR(10)

SELECT *
FROM Orders

GO

-- Update Customer 3
-- Change CustomerID from 3 for 30 

UPDATE Customers
SET CustomerID = 30
WHERE CustomerID = 3

PRINT CHAR(10) + 'Customers table after update Customer 3'+ CHAR(10)

SELECT *
FROM Customers

PRINT CHAR(10) + 'Orders table after update Customer 3'+ CHAR(10)

SELECT *
FROM Orders

GO

DROP TABLE Orders

DROP TABLE Customers
Original Customers table

CustomerID  CustomerName
----------- --------------------
1           MyComp corp.
2           ACME Inc.
3           NewDotCompany Ltd. 


Original Orders table
OrderID     CustomerID  OrderDate
----------- ----------- ------------------------------
1           1           2000-11-12 23:59:00
2           1           2000-11-12 23:59:00
3           2           2000-11-12 23:59:00
4           2           2000-11-12 23:59:00
5           2           2000-11-12 23:59:00
6           3           2000-11-12 23:59:00
7           3           2000-11-12 23:59:00


Customers table after update Customer 3

CustomerID  CustomerName
----------- --------------------
1           MyComp corp.
2           ACME Inc.
30          NewDotCompany Ltd.


Orders table after update Customer 3

OrderID     CustomerID  OrderDate
----------- ----------- ------------------------------
1           1           2000-11-12 23:59:00
2           1           2000-11-12 23:59:00
3           2           2000-11-12 23:59:00
4           2           2000-11-12 23:59:00
5           2           2000-11-12 23:59:00
6           30          2000-11-12 23:59:00
7           30          2000-11-12 23:59:00 

Transact-SQL–Specific Integrity Structures

Transact-SQL language provides an alternative to the CHECK and DEFAULT constraints with the RULE and DEFAULT objects. RULE and DEFAULT objects are not ANSI standard, so it is advisable to use constraints as a general way to provide the same functionality.

One of the reasons to use these Transact-SQL objects is to create self- contained user-defined data types, including not only the data type, but also the DEFAULT value and the RULE to check for domain integrity. If a column uses one of these self-contained user-defined data types as a data type, this column will inherit the DEFAULT definition and the RULE definition as well.

User-defined data types were covered in Chapter 2.

Using DEFAULT and RULE objects can help during the development process, if the same condition must be applied to multiple columns. However, remember that they are not ANSI compliant.

DEFAULT Objects

DEFAULT objects are similar to the DEFAULT definition of a column, but you can create a DEFAULT object independently of any column and bind it to specific columns or user-defined data types later.

To create a DEFAULT object, you use the CREATE DEFAULT statement, providing a unique name for the DEFAULT object and defining the object as a constant, built-in function or any valid scalar expression.

To delete a DEFAULT object, you must use the DROP DEFAULT statement. You cannot drop a DEFAULT object if it is used anywhere in your database.

To bind a DEFAULT object to a field or user-defined data type, you must use the sp_bindefault system stored procedure, and the sp_unbindefault disconnects a bound DEFAULT object from a field or user-defined data type. Only one DEFAULT definition or DEFAULT object can be defined per column; binding a new DEFAULT object to a column overrides the existing one.

You can see a complete example of how to use DEFAULT objects in Listing 7.19.

Code Listing 7.19. Create Independent DEFAULT Objects and Bind Them Later to Any Field or User-Defined Data Type

							
-- Create a DEFAULT object using a constant

CREATE DEFAULT NoSales
AS 0
GO

-- Create DEFAULT objects using expressions
-- based on built-in functions

CREATE DEFAULT ThisMonth
AS Month(CURRENT_TIMESTAMP)
GO
CREATE DEFAULT UserDB
AS SYSTEM_USER
+ '- '+ DB_NAME(DB_ID())
GO

-- Create two User-Defined Data Types

EXEC sp_addtype 'UDDTLoginDB', 'nvarchar(256)', 'NULL'

EXEC sp_addtype 'UDDTSales', 'money', 'NULL'
GO

-- Create a table to test the DEFAULT objects
-- and the User-Defined Data Types

CREATE TABLE TestDefaults(
ID int NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
TotalSales money NULL,
SalesMonth tinyint NULL,
WhoWhere UDDTLoginDB)
GO

-- Insert a new empty row in the table

INSERT TestDefaults
DEFAULT VALUES

PRINT char(10) + 'No defaults defined'+ CHAR(10)

SELECT *
FROM TestDefaults
GO

-- Bind the NoSales DEFAULT object
-- to the TotalSales field in the TestDefaults table

EXEC sp_bindefault 'NoSales', 'TestDefaults.TotalSales'
GO

-- Insert a new empty row in the table

INSERT TestDefaults
DEFAULT VALUES
PRINT  CHAR(10) + 'Only DEFAULT on TotalSales defined'+ CHAR(10)

SELECT *
FROM TestDefaults
GO

-- Bind the ThisMonth DEFAULT object
-- to the SalesMonth field in the TestDefaults table 

EXEC sp_bindefault 'ThisMonth', 'TestDefaults.SalesMonth'

GO

-- Insert a new empty row in the table

INSERT TestDefaults
DEFAULT VALUES

PRINT  CHAR(10) + 'DEFAULT defined on TotalSales and SalesMonth'+ CHAR(10)

SELECT *
FROM TestDefaults
GO

-- Bind the UserDB DEFAULT object
-- to the UDDTLginDB User-Defined Data Type

EXEC sp_bindefault 'UserDB', 'UDDTLoginDB'

GO

-- Insert a new empty row in the table

INSERT TestDefaults
DEFAULT VALUES

PRINT  CHAR(10) + 'DEFAULT defined on TotalSales, SalesMonth'
PRINT 'and the UDDTLoginDB User-Defined Data Type'+ CHAR(10)

SELECT *
FROM TestDefaults
GO

-- Add a new column to the TestDefaults table 
-- Using the UDDTSales data type
ALTER TABLE TestDefaults
ADD ProjectedSales UDDTSales
GO

PRINT CHAR(10) + 'Add an empty field using the UDDTSales data type'+ CHAR(10)

SELECT *
FROM TestDefaults
GO

-- Bind the NoSales DEFAULT object
-- to the UDDTSales User-Defined Data Type
-- for future columns only

EXEC sp_bindefault 'NoSales', 'UDDTSales', 'futureonly'
GO

-- Insert a new empty row in the table

INSERT TestDefaults
DEFAULT VALUES

PRINT CHAR(10) + 'DEFAULT defined on UDDTSales data type as futureonly'
PRINT 'does not affect the existing fields using this UDDT'+ CHAR(10)

SELECT *
FROM TestDefaults
GO

-- Drop everything in order
-- Table first
-- UDDT next
-- DEFAULT last

DROP TABLE TestDefaults

EXEC sp_droptype 'UDDTSales'

EXEC sp_droptype 'UDDTLoginDB'

DROP DEFAULT NoSales

DROP DEFAULT ThisMonth

DROP DEFAULT UserDB

Type added.
Type added.

No defaults defined

ID     TotalSales       SalesMonth WhoWhere
------ ---------------- ---------- --------------
1      NULL             NULL       NULL

Default bound to column.

Only DEFAULT on TotalSales defined

ID     TotalSales       SalesMonth WhoWhere
------ ---------------- ---------- --------------
1      NULL             NULL       NULL
2      .0000            NULL       NULL

Default bound to column.

DEFAULT defined on TotalSales and SalesMonth

ID     TotalSales       SalesMonth WhoWhere
------ ---------------- ---------- --------------
1      NULL             NULL       NULL
2      .0000            NULL       NULL
3      .0000            11         NULL

Default bound to data type.
The new default has been bound to columns(s) of the specified user data type.

DEFAULT defined on TotalSales, SalesMonth and the UDDTLoginDB User-Defined Data Type

ID     TotalSales       SalesMonth WhoWhere
------ ---------------- ---------- --------------
1      NULL             NULL       NULL
2      .0000            NULL       NULL
3      .0000            11         NULL
4      .0000            11         sa - ByExample

Add an empty field using the UDDTSales data type
ID     TotalSales       SalesMonth WhoWhere         ProjectedSales
------ ---------------- ---------- ---------------- ---------------
1      NULL             NULL       NULL             NULL
2      .0000            NULL       NULL             NULL
3      .0000            11         NULL             NULL
4      .0000            11         sa - ByExample   NULL 

Default bound to data type.

DEFAULT defined on UDDTSales data type as future only does not affect the existing fields
 using this UDDT

ID     TotalSales       SalesMonth WhoWhere         ProjectedSales
------ ---------------- ---------- ---------------- ---------------
1      NULL             NULL       NULL             NULL
2      .0000            NULL       NULL             NULL
3      .0000            11         NULL             NULL
4      .0000            11         sa - ByExample   NULL
5      .0000            11         sa - ByExample   NULL

Type has been dropped.
Type has been dropped. 

Rule Objects

RULE objects are similar to CHECK constraints. However, you can create a RULE object independently of any column and bind it later to specific columns or user-defined data types.

To create a RULE object, you use the CREATE RULE statement, providing a unique name for the RULE object and defining the object as any expression that returns TRUE or FALSE.

To delete a RULE object, you must use the DROP RULE statement. You cannot drop a RULE object if it is used anywhere in your database.

To bind a RULE object to a field or user-defined data type, you must use the sp_bindrule system stored procedure, and the sp_unbindrule disconnects a bound RULE object from a field or user-defined data type.

You can bind only one rule to a user-defined data type or a table field. However, a rule can coexist with one or more CHECK constraints in a field; in this case, all the conditions will be checked. If you bind a new rule to a field or user-defined data type, the old rule will be unbound automatically.

You can see an example of how to use RULE objects in Listing 7.20.

Code Listing 7.20. Create Independent RULE Objects and Bind Them Later to Any Field or User-Defined Data Type

							
-- Define a Table to test RULE Creation

CREATE TABLE NewEmployees (
EmployeeID int NOT NULL,
EmployeeName varchar(50) NOT NULL,
PostCode char(5) NOT NULL )
GO

-- Create the RULE object

CREATE RULE RUPostCode
AS
(@PCode LIKE '[0-9][0-9][0-9][0-9][0-9]')
GO

-- Bind the RULE to the PostCode column

EXEC sp_bindrule 'RUPostCode', 'NewEmployees.PostCode'
GO

-- Insert data in the table to test the RULE 

INSERT NewEmployees
VALUES (1, 'Paul', 'GL513')

INSERT NewEmployees
VALUES (2, 'Eladio', '01380')

SELECT *
FROM NewEmployees

GO


DROP TABLE NewEmployees
GO

DROP RULE RUPostCode
GO

Rule bound to table column.
Server: Msg 513, Level 16, State 1, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE
 statement. The statement was terminated. The conflict occurred in database 'ByExample',
 table 'NewEmployees', column 'PostCode'.
The statement has been terminated.
EmployeeID  EmployeeName                   PostCode
----------- ------------------------------ --------
2           Eladio                         01380 

What's Next?

This chapter covered the creation and use of structures to enforce data integrity.

Chapter 8 covers the creation of stored procedures, where you can test the integrity of the data before attempting any modification, having extra data control and access to more complex condition checking.

Chapter 9 covers triggers, which is another way to enforce data integrity. In that chapter, you will see how to create triggers to enforce domain integrity and referential integrity.

User-defined functions are covered in Chapter 10. It is possible to use UDF as part of constraint definitions. This new feature gives you tremendous flexibility in the definition of DEFAULT and CHECK constraints.