16. Establish and use preset values, conditional constraints and rules
default value
Conditional constraint
Rule object
Summary of this chapter
Preset values, conditional constraints, and rules are all selective properties you can define the data line definition in the data sheet. In Chapter 15, I have learned how to use the ALTER TABLE command to add a preset information line. This chapter covers the establishment and modification of the preset. Looking back, setting the preset value (default) refers to the input a clear value in the data line that is not clearly set. The condition constraint is a method for identifying valid data line values (in order to reject invalid values). This chapter will introduce five types of conditional constraints and how to use T-SQL and Microsoft SQL Server Enterprise Manager to set up and modify preset values and conditional constraints, however, using Enterprise Manager is simpler.
The preset value will explain why the preset value needs to be set in the data line of the data sheet. For example, if you want to insert a data column in an information list that can be included in the NULL Data, these data will be entered as a NULL value. However, if these data is defined as NOT NULL, and when the data is inserted, there is no value for the data line, there will be an error message to remind you that NULL cannot be inserted into the batch line. At this time, the preset value can be used to play its effect. The preset value can replace the NULL value at this time to avoid error messages. Microsoft SQL Server 7.0 allows you to define a preset value for each of the data sheets. The data line with TimeStamp data type or Identity, the ROWGUIDCOL property cannot define a preset value, as these data must be a special value. The value specified in the preset value definition must also be related to the data type in the data line.
There are several ways to establish and modify preset values. In this part, we first introduce how to define a preset value when T-SQL is established, and how to modify the data line through new or modify the preset value; then introduce the same ENTERPRISE Manager to perform the same jobs. In Chapter 15, we have introduced how to add a preset data line for the data sheet, and the impact of this information line on the existing information columns in the data sheet. Here, we will provide an example of the impact of the options available and the new pre-set value on the existing data line in the data sheet.
Use T-SQL to define and modify the preset value
You can define the preset value for the following three statements: CREATE TABLE, ALTER TABLE, or CREATE DEFAULT. SQL Server 2000 uses Create default to establish a preset object, keep back compatibility. When using this method, SQL Server stores the data sheet and the object, so it must be tied to the information sheet through the system pre-deposit program sp_bindefault. When you delete a data sheet, the default definition is automatically united from the data sheet, but the preset object still exists. When using Create Table or ALTER TABLE, SQL Server stores the preset object definition and information table if the data table is deleted, and the preset object will be automatically deleted without having to take another steps. Therefore, it is often not recommended to use Create Default. However, when using the same preset value for multiple data lines, use preset objects may also bring benefits.
When performing T-SQL, SQL Query Analyzer should be used because its execution result can be displayed on the user graphics interface. This feature is more convenient to read in the command window.
Establish a preset using Create Table
Use the CREATE TABLE command to establish a preset value in the data line is more commonly used standard techniques. The following statement establishes a preset data sheet in MyDB, and simultaneously defines two data lines: A information behavior char type; B data behavior int type.
Use mydb
Create Table MyTable
(
Columna char (15) Null Default 'N / A',
Columnb Int Null Default 0
)
Go
The preset value N / A in columna is incompatible with the CHAR data sheet of the data; the same, the preset value of 0 in columnb is 0 and the INT data type of the data line. If we insert a data column in the data sheet without specifying a value for both of the two data lines of Columna and Columnb, the preset value will be automatically taken. So if you want to keep null, you must understand that you want to insert a NULL value. Because this two data can contain NULL values, it is allowed to insert a null value is allowed. However, if the data is defined as NOT NULL, the NULL value cannot be inserted.
Use the alter table to modify the preset value Use the alter table command to modify the preset value definitions in the data line or add a list of information. To change the defined preset value, you must first delete the existing preset value, then add a new preset value (if you use Enterprise Manager, it will be simple, because using Enterprise Manager is not The step of deletion is required). If you create an unnamed preset value with the CREATE TABLE command, or use Enterprise Manager (will be described later in this chapter), SQL Server will automatically name the preset value. To know what your name is allocated for the preset value so that you can use T-SQL to delete it, you can perform the sp_help program as follows:
Use mydb
Go
SP_HELP MyTable
Go
All MyTable preset values are listed in the consTRAINT_NAME Data Line in Figure 16-1:
Figure 16-1 Output of the sp_help program
Suppose we have to change the preset value of Columna from N / A to Not Applicable. Remember to first delete the preset value of the existence and then add a new one. The following command can delete the preset value:
Alter Table MyTable
DROP constraint df_mytable_columna_2b3f6f97
Now you can add a preset value using the following command, this time you name it yourself,:
Alter Table MyTable
Add constraint DF_MYTABLE_COLUMNA
Default "Not Applicable" for Columna
Go
When changing existing preset values, all existing columns will keep the original preset value. Only newly inserted columns use new preset values.
As mentioned in Chapter 15, you can also use the alter table command to add a complete new information to the existing information sheet, as shown below:
Alter Table MyTable
Add Columnc Tinyint Not Null Default 13
Go
Now the MyTable Example Table has added a number of data line Columnc Data, its preset value is 13. Because Columnc is a new data line defined as NOT NULL, the new data line of the existing data line in the data sheet will be assigned to the preset value 13.
If the new data is allowed to be set to a null value, the new information line of the existing data line in the data sheet will be assigned to a null value. If we want to insert an existing column with a preset value rather than NULL, you should use the whit value in Default, as shown below:
Alter Table MyTable
Add Columnc Tinyint Null Default 13 with VALUES
Go
The with value command will force all the new data lines of all the existing columns in MyTable accept the preset value 13 instead of the original NULL value.
Now, we have learned how to build a default definition with the information sheet stored together. Below we will learn how to build a preset object stored separately with Create Default. Create default and sp_bindefault
You can also use the T-SQL CREATE DEFAULT command to create a default object, thereby adding or modifying the preset value of the existing funds line. After establishing a preset value, use the system preparation program sp_bindefault to set it to the data line or to the user's customs. As previously mentioned, the purpose of reserving this method in SQL Server 2000 is to back compatibility, so it is not a good method. However, this method is more effective if you want to use the same preset value data in different data tables.
Here, an example is described, using Create Default to establish a preset value of DF_NOT_APPLICABLE, which is N / A. This preset object is established in the MYDB database and is tested to the MYTABLE COLUMNA (there is no preset value in the assumption). The syntax using the CREATE DEFAULT is as follows:
Create default default_name as constant_expression
The syntax of sp_bindefault is as follows:
sp_bindefault 'default_name' table.column | user_defined_datattype
[", futureonly"]
The parameter default_name is the name of the preset object. Table.column is a data line to assign a preset object.
The following T-SQL statement can establish an example of the preset object and set it to a MYTABLE columna:
Use mydb
Go
CREATE DEFAULT DF_NOT_APPLICABLE AS 'N / A'
Go
SP_BINDEFAULT "DF_NOT_Applicable", "MyTable.Columna"
Go
If there is already a preset value in Columna, SQL Server will return an error message to notify you that the preset item is not allowed to have a preset data line. You first need to delete the original preset value, then set the new preset object to the data line (the process of deleting the preset object using Drop DEFAULT will be described later).
You can also establish a preset object and take this object to the user's custom data. When presets the item to the user's custom, you can use the sp_bindefault's FutureOnly option. This option prevents the existing user-reserved part of the information line accepts this new preset value, and only the newly established user-reserved information line can accept the associated preset value. If Futureonly is not specified, SQL Server is tuned to all preset values to all existing and newly established user-reserved data lines.
For example, let us build a user-reserved value of the user-based customs and name of DF_AREA_CODE, which is 786; then set the preset value to the user's custom data type (user) Self-order data type is based in Chapter 10). Because this is a new user self-reservation, there is no information line yet, and there is no needonly option, but we still keep it because it does not have any effect. The statement will be as follows:
SP_ADDTYPE "Area_code", "Char (3)", "Not Null"
Go
Create default df_area_code as 786GO
SP_BINDEFAULT "DF_Area_code", "Area_code", "FutureOn"
Go
To view the data type of the preset object, you can use the sp_help system program, as shown in Figure 16-2.
Figure 16-2 SP_HELP Query Analyzer results.
SP_unbindefault
To release the preset object from the data line or user custom data type, you can use sp_unbindefault. For example, to delete the preset value of the mixing with the columna data line in MyTable: sp_unbindefault, its statement is as follows:
Sp_unbindefault "MyTable.Columna"
Go
Use the following statements to unite the preset value from the user's self-reserve data type, AREA_CODE:
SP_UNBINDEFAULT "Area_code"
Go
When the above programs are implemented, all preset value attributes that have been customized by the user's custom data type doing AREA_CODE will be removed.
You can use different sp_bindefault statements to set the preset object to multiple data lines. Similarly, as long as you do not delete the preset object, you can freely relieve or lay a preset of a list of data. With the Drop default statement, you can completely delete a preset object as follows:
Drop default df_area_code
Go
Once the preset object is deleted, it cannot be retrieved. If you want to use it again, you must re-establish the item using Create Default.
Use Enterprise Manager to define and modify the preset value
As shown in Chapter 15, using Enterprise Manager to establish and modify the data sheet may be the easiest way. When establishing or modifying the information list or data line with Enterprise Manager, SQL Server automatically performs the appropriate T-SQL command to complete the task for you (on how to use Enterprise Manager to establish a data sheet according to the class, see Chapter 10). This section will learn how to specify the preset value definition and modify the details of the preset value of the preset value, and how to establish a preset object. Let us say from the specified and modified preset value definitions.
Specify and modify the default definition
Assume that there is a data sheet named Product_Info in the MyDB database. The structure of the data sheet is shown in Figure 16-3 (see Chapter 10) using the Enterprise Manager to establish a list of information sheets).
Figure 16-3 Design Data Sheet Window for Product_info Table
To define a preset value, just type the preset value of the data line in the storage grid under the preset value. Note that the preset value of the Desciption data line in Figure 16-3 is defined as 'N / A', which means that the characteristics of the product have not yet specified. At the same time, pay attention to the preset value is enclosed in parentheses.
Change preset is also very simple. Only replace the original preset value with the new preset value, then press the archive button to save the operation. Figure 16-4 Displaying the preset value of the Desciption is 'not available'; Figure 16-5 shows a preset value General Merchandise in Product_name.
Description
In the preset value storage grid, enter the string, must be placed in single quotes, otherwise the SQL Server error message is displayed.
Figure 16-4 Design data sheet window, display modified preset value
Figure 16-5 Design data sheet window, display new preset value
Similar to T-SQL, when using Enterprise Manager to add or modify the preset value on the extension of the existing money, it does not affect the existing information in the data sheet, only the newly inserted data column uses a new preset value. If you add a data line for the data table and specify a preset value, the data in any existing information column will get the preset value of the new data line in the preset value of the NULL; if allowed to be null Value, the new data line in the existing column will be assigned to a null value. To enable the new data to allow the null value, and forcibly insert the preset value into all existing columns, refer to the method described in front of this chapter using the ALTER TABLE Modifying Preset Value> Some methods. Establish and manage preset objects
You can also use Enterprise Manager to create preset objects and review existing components. To view the existing preset object, open Enterprise Manager, expand the server and database to use, and press the preset. All existing preset objects will be displayed in the right pane, as shown in Figure 16-6. Note that you will be able to see DF_NOT_Applicable and DF_APPLICABLE and DF_AREA_CODE presets items established in front of this chapter.
Figure 16-6 Viewing existing preset objects
To establish a new preset object using Enterprise Manager, and associate the preset object to the information line or the user's custom data type, you must follow the steps:
Expand the servo and database, press the right button on the preset and select the new preset on the flash function table to display the preset properties window, as shown in Figure 16-7. Name this preset object DF_NONE and assigns 'none' for its value. After the operation is complete, press OK.
Figure 16-7 Preset Property Window
To serve the preset object to the user's custom data type, or a data line, press the right button on the first pane of Enterprise Manager (this example is DF_NONE), and from the quick function table Select content. The preset attribute window will appear again, but this time you can use the UDT (user custom data type) and the line button. Press the UDT button to display the dialogue of the data type of the user to the user's customs, as shown in Figure 16-8. This dialogue will display all users' custom data, from the list, select the user's custom value to associate the user's custom value. In Figure 16-8, you can see Area_code and Brand_Type data type. If many user custom data files have been defined, they also appear in this list. After the operation is complete, press the set to press OK to return to the preset property window. To set the preset value to the data line, press the Data Row button to set the preset value to the dialogue of the data line will be displayed. Now choose the information to be tied. First select the data table name from the data sheet drop-down list, then select the data line name to be served in the preset value from the undesirable data line list. Then press the new increase (Figure 16-9 shows the dialogue of the PRICE Data line) from the series data line in the Product_info table. Press to determine the returned preset property window, then press OK to close the preset property window.
Figure 16-8 Data-type Division of "Say Pre-Set Value to User Custom" Dialogue
Figure 16-9 "Serving preset value to the data line" dialogue
To release the preset object from the user's self-reservation, you can turn on the preset preset preset value to the user's self-binding, and then clear the tufaction. To release the preset value of the preset object, turn on the preset value to the data line dialog, select the data line name, press to remove.
To delete a preset object, you must first unify the preset object from other objects. If you are trying to delete the preset of the item tie, SQL Server will return to an error message. To delete a preset object, press the preset in the left pane of Enterprise Manager, press the right button on the preset item name, and select Delete in the quick function table, then press the discharge of the object dialogue. All. To this end, we have described preset objects, which will begin to introduce the condition constraints.
Condition Constraint Conditions Constrained for Automatic Maintenance of Data. For example, you can constrain an integer data line condition in the range of 1 to 100, so that the value exceeded this range cannot be accepted (CHECK function can set this condition constraint). The condition constraint on a single data line is called the Column Constraint, which only restrained the value range of the information line. Conditions affecting two or more data lines are called data table conditions constraints, which ensures that the combination of data lines meets the requirements of conditional constraints. Five types of conditions constraints for NOT NULL, UNIQUE, PRIMARY KEY, Foreign Key, and Check.
Use T-SQL to establish and modify the conditional constraints in this section, we will learn a variety of types of conditional constraints and how to build conditional constraints with T-SQL. The later part will learn the same process with Enterprise Manager. NOT NULL
NOT NULL condition constraints is quite simple. In fact, we have already contacted in the examples of this chapter and the previous chapter, and the NOT NULL condition constraint placed in the data line can avoid inserting a null value in the data line (this is opposite to the NULL condition constraint that allows the NULL value). When you use the T-SQL to modify the data line, you can specify the information list or you will specify the information behavior after the task list (see Chapter 10 for details on the Database Database Table section).
Unique
UNIQUE condition constraints are used to ensure that there is no repetition in one or more data lines. In other words, strengthen the uniqueness of the value in the data line. To strengthen this uniqueness, SQL Server creates a unique non-clustered index in the UNIQUE conditional constraint. You can specify an index is a cluster or non-collected, but to remember that a data sheet only allows a clustered index.
Description
This chapter will often use the clustered index and non-cluster index, and if you have the meaning of them, Chapter 17 will detail these two types of indexes.
UNIQUE condition constraints can be used for the information line of any non-Primary Key condition constraint (will be described in the next part), and the primary key condition constraint has enhanced the uniqueness of the value. UNIQUE condition constraints can be used to allow NULL values, and the Primary Key condition is constrained. UNIQUE condition constraints will ignore the NULL value. A line of data restricted by UNIQUE can be restrictions by the Foreign Key Conditions (described in this chapter "section
To create a unique condition constraint with T-SQL to create a CREATE TABLE or ALTER TABLE command. For example, the following statement will establish a Customer Data Sheet and establish UNIQUE condition constraint on the SSN data line as a clustered index:
Create Table Customer
(
First_name char (20) Not null,
MID_INIT Char (1) NULL,
Last_name char (20) Not null,
SSN Char (11) Not Null Unique Clustered, Cust_Phone Char (10) NULL
)
Go
The previous Create statement uses a data line condition constraint. The following example will also create a Customer Data Sheet, and this time new Unique Table condition constraints on first_name, MID_INIT, and LAST_NAME Data:
Create Table Customer
(
First_name char (20) Not null,
MID_INIT Char (1) NULL,
Last_name char (20) Not null,
SSN Char (11) Not Null Unique Clustered,
Cust_phone char (10) NULL,
ConsTRAINT UQ_FULL_NAME UNIQUE NONCLUSTERED (first_name,
MID_INIT, LAST_NAME)
)
Go
UNIQUE Data Sheet Condition Constraints (Conditional Constraints for Data Rows) Ensure that all values in the data line are unique. That is, this database cannot accept the identical customer of First Name, MID INITIAL, and Last Name. Note that the Unique Table Condition Constraint here is a non-clustered index because there is already a clustered index in the SSN.
To add a Unique condition constraint for the existing data table, you must use the alter table command. If you want to modify the existing data line through the new Unique condition constraint, all existing columns in the information table must maintain a single or NULL value in these data lines, otherwise an error message is displayed, prompting you to add UNIQUE conditions. constraint. Suppose we have established a Customer Data Sheet without condition constraints. Below is two sets of orders for new information line conditions constraints and data sheet conditions:
ALTER TABLE CUSTOMER
Add Constraint UQ_ssn Unique Clustered (SSN)
Go
ALTER TABLE CUSTOMER
Add constraint uq_full_name unique nonclustered (first_name,
MID_INIT, LAST_NAME)
Go
Similarly, you can only add condition constraints for information sheets when existing information meets the conditional constraints.
To use T-SQL to modify the Unique condition constraints in the data line or data sheet, you must first delete the condition constraint and then re-establish. Like the preset value, in order to easily complete the query and delete condition constraints, you should use the descriptive name to which the SQL Server assigns them. To establish and modify the conditional constraints in the Main index key The main index key condition constraint is used to specify the primary key (Primary Key) of one or more data lines in the data sheet, and can distinguish a certain information column. Since the setting of the main index key is to identify the information column, the main index key condition constraint cannot be null; this is constrained with the UNIQUE condition. When defining the main index key condition constraint for a set of data, it must be unique to each of the information columns. Like UNIQUE conditions, the primary key condition constraint does not allow repeated values and automatically establish a single index in the main index key. You can also specify the index of the main index key is a cluster or a non-cluster; if there is no special designation, the data sheet will be automatically preset as a clustered index. Each data table has only one primary key condition constraint. The Identity Data Line is the best choice for the main index key, or the value of the data line (or a set of data) is a unique value relative to other information columns, you can set the main index key. For example, in the Example Data Table Customer, we can set the SSN data as a primary index key, rather than establish a Unique condition constraint in the data line. The primary key condition constraint does not allow NULL values, so the uniqueness of the value in the SSN data line is enhanced, and the coefficient index is automatically established in the main index key data line. The following T-SQL command demonstration specifies the method of the SSN data line as the main index key. Through this method, SQL Server will actively name this Primary Key condition constraint, so this is not the best way, because you may need to delete this key value through the name (but you can't know what name this Primary Key) . Create Table Customer ( First_name char (20) Not null, MID_INIT Char (1) NULL, Last_name char (20) Not null, SSN Char (11) PRIMARY Key, Cust_phone char (10) NULL ) Go Another possible way to name it to increase the constraint keyword. Name the main index key with PK_SSN with the following command: Create Table Customer ( First_name char (20) Not null, MID_INIT Char (1) NULL, Last_name char (20) Not null, SSN Char (11) Constraint PK_SSN Primary Key, Cust_phone char (10) NULL ) Go You can also specify the Primary Key condition constraint after defining the data line of all the information sheets. The name of the data must be in parentheses and specified after constraint, the syntax is as follows: Create Table Customer ( First_name char (20) Not null, MID_INIT Char (1) NULL, Last_name char (20) Not null, SSN Char (11), Cust_phone char (10) NULL, ConsTRAINT PK_SSN Primary Key (SSN) ) Go Use the alter table command to add Primary Key conditional constraints for information on whether the primary key condition is constrained. The following is a new Primary Key for the Customer Data Sheet: ALTER TABLE CUSTOMER Add Constraint PK_SSN Primary Key Clustered (SSN) Go The above command includes selective keyword clustered, which is to indicate that despite the preset of the data table, we can still establish a clustered index in the main index key data line. To delete the Primary Key condition constraint, you must use the ALTER TABLE command and the Drop Constraint statement. Below to delete the conditional constraints of the SSN data line: ALTER TABLE CUSTOMER DROP Constraint PK_SSN Go You should pay special attention to that only the condition constraint name is required in the Drop Constraint statement. To use the T-SQL command to modify the existing Primary Key condition constraints in the data table, you must first use the ALTER TABLE ... DROP CONSTRAINT to remove the existing condition constraint and the ALTER TABLE ... Add Constraint statement to new condition constraints to modify the information sheet. . External index key The external index key (FOREIGN Key) condition constraint is used to identify the correlation between the two-piece data sheet, the identification method is the information table A external index key will refer to the candidate index key in the data table B (Candidate Key, can be one or more Row). To insert a data column into a data sheet with an external index key, the inserted data sheet will check the candidate index key in the reference data sheet, if the external index key in the two data sheets does not match the value of the candidate index key, Inserting is not allowed; in turn, insertion is allowed. When updating the information column of the information sheet in the reference data or an external index key, the external index condition constraint is also checked, so when the candidate index key or an external index key is updated, the set condition constraint cannot be violated. This rule has an exception: Updating the NPDATE CASCADE option in the T-SQL CREATE TABLE statement when the reference information is updated. This selection will be introduced in this chapter In addition, if you want to remove a data column from the reference data sheet, the external index key is also checked. If the information you want to delete is column with a data sheet with an external index key, it cannot be deleted. In other words, each of the information in the external index key data sheet must have a relatively data column in the reference data sheet, and when the information column to be deleted is referred to by another information sheet, it cannot be deleted. This information is listed. This rule has only one exception: Updating the reference information list using the ON DELETE CASCADE option in the T-SQL CREATE TABLE statement. This option will be introduced in The external index key data sheet can only refer to the data sheet in the data sheet with the Primary Key condition constraint or the Unique condition constraint. If you want to reference an external index key that does not contain the above condition constraints, SQL Server will return the error message. The data type and size of the external index key data must be consistent with the required information. Please refer to the following example to further understand the use of an external index key. First create a data sheet, named Items, where the Item_ID data is the main index key, as shown below: CREATE TABLE ITEMS ( Item_name char (15) Not null, Item_id Smallint Not Null Identity (1, 1), Price Smallmoney Null, Item_Desc varchar (30) Not null default 'none' ConsTRAINT PK_ITEM_ID PRIMARY Key (item_id) ) Go Next, we will establish an Inventory's data table, which is known as an external index key called FK_Item_ID, and the external index key references the item_id of the Items data sheet, as shown below: CREATE TABLE Inventory ( Store_ID Tinyint Not Null, Item_id Smallint Not Null, Item_quantity Tinyint Not Null, ConsTRAINT FK_ITEM_ID FORGIEN Key (item_id) References Items (Item_ID) ) Go To observe the correlation between the two data sheets, please refer to the database chart shown in Figure 16-10 (how to establish a database chart, see Chapter 15). In this example, Items is a reference table with a candidate index key item_ID. Since it is the main index key of the data sheet, there is no other UNIQUE condition constraint in the data sheet, so it is the only possible candidate index key. Remember, only the main index key and the unique condition constraint may be a valid candidate index key. The Item_ID of the Inventory Data Sheet has been defined the external index key, thus establishing the association between the two data sheets. The data type of these two related data is small. Establish an external index key in the Item_ID data line of the Inventory table, ensuring that when a value does not exist in the items data sheet, you cannot insert information into the data sheet, and you cannot delete the outside of the data table inventory from the data sheet items. The data column referenced by the index key. In other words, if a project exists in the Items data sheet and the Inventory data sheet, the item cannot be removed from the Items data list or in the Inventory table. Figure 16-10 Displaying the database chart of external index key intervals of Items and Inventory data To modify the Foreign Key condition constraint using the T-SQL command, you must first remove the old condition constraint, and then create a new condition constraint using the alter table command. This approach is similar to a method of modifying the Primary Key condition constraint. Here is the old condition constraint of the Inventory sheet before deleting, and then the condition constraints are added: ALTER TABLE Inventory DROP continRAINT FK_ITEM_ID Go ALTER TABLE Inventory Add constraint fk_item_id foreign key (item_id) References Items (item_id) Go When you add Foreign Key condition constraints in the existing line, SQL Server checks the existing information in the data table to ensure that the value of the external index key data line is constrained by the value of the reference data sheet in addition to the NULL value. UNIQUE condition constraints. When establishing the Foreign Key condition constraint, you can use the ALTER TABLE's With Nocheck option, then SQL Server will not verify the existing value as follows: ALTER TABLE Inventory WITH NOCHECK ADD CONSTRAINT FK_ITEM_ID Foreign Key (item_id) References Items (item_id) Go The WITH NOCHECK option avoids the list of columns in the SQL checking table. As such, no matter what the existing value is, the condition is added to the information sheet. After the new condition constraint, the integrity of the external index key will also be enhanced. note Be careful when using the with nocheck option. Because when you expect existing information, existing data contains values for conflict with conditional constraints, you will not be able to update existing information. You can also control whether the Foreign Key condition constraint is enabled. If you want to insert a list of conflicts with the existing condition conflict, you can temporarily make the condition constraints are invalid, and then re-enable the condition constraint after completing the insert. The NOCHECK keyword represents ignoring the conditional constraint (conditional constraints fail); the check keyword indicates that the conditional constraint takes effect. The following command uses Nocheck and check keywords to control whether the Foreign Key condition constraint is effective: ALTER TABLE Inventory NOCHECK CONSTRAINT FK_ITEM_ID - Condition Constraint Invalid GO - Insert this information you need here ALTER TABLE Inventory Check constraint fk_item_id - Re-reply condition constraint Go Description Try not to insert the information on conflict with the Foreign Key condition. Failure to do so may result in the conflict of conflicts in the future information sheets that cannot be updated. CHECK CHECK condition constraints are used to limit the allowable range of the value of the data line. When the Boolean search criteria specified in the conditional constraint is True, the value inserted or modified in the data line is effective. For example, if we want to limit the PRICE data line of the Items data sheet to allow values to be between $ 0.01 to $ 500.00, the following statements should be used: CREATE TABLE ITEMS ( Item_name char (15) Not null, Item_id Smallint Not Null Identity (1, 1), Price Smallmoney Null, Item_Desc varchar (30) Not null default 'none' ConsTRAINT PK_ITEM_ID PRIMARY Key (item_id), Constraint CK_PRICE CHECK (price> = .01 and PRICE <= 500.00) ) Go Note that we allow NULL in the Price Data Line and have Check condition constraints in the data line. Since SQL Server can distinguish NULL values and other values, the Price Data line can still allow NULL values while CHECK condition constraints. At the same time, it should be noted that we are named CK_PRICE. We have seen before that the condition constraints can be simply deleted and re-established with T-SQL later. For example, modify the value range from $ 1.00 to $ 1000.00, you can use the following statement: Alter Table Items DROP CONSTRAINT CK_PRICE Go Alter Table Items Add constraint ck_price check (price> = 1.00 and PRICE <= 1000.00) Go The second ALTER TABLE command should be the same as the command used when new condition constraints for existing Items data sheets. It is the same for the new CHECK conditional constraints and new Foreign Key condition constraints. All existing columns will be checked according to condition constraints. If all returned values are not true, the conditional constraint will not be added to the data sheet, and SQL Server will return the error message: pointed out the Alter Table Statement and Check Conditions constrained conflict. If you have to add condition constraints, use the with nocheck to specify the existing information column does not take effect, and the future insertion and modification of the information will take effect. note Not recommended for use of Nocheck because columns that do not meet the conditional constraints may not be updated in the future. Below is an example of using with nocheck when adding a CK_PRICE condition constraint: Alter Table Items With Nocheck Add Constraint CK_PRICE Check (price> = 1.00 and price <= 1000.00) Go Like the Foreign Key condition constraint, use the Check and Nocheck keywords in Alter Table, you can also control whether the Check condition constraint is effective. You may want to insert a price that exceeds the specified range but still valid. The following example will stop using the CK_PRICE condition constraint, then take effect: Alter Table ITEMS NOCHECK CONSTRAINT CK_PRICE - Conditional Constraint Invalid Go - Insert the information column here Go Alter Table Items Check constraint CK_PRICE - Re-enable condition constraint Go Description Only the conditional constraints of the Check and Foreign Key types can be used to control whether it takes effect. Establish and modify the condition constraint with Enterprise Manager This part will learn how to build, modify, and delete conditional constraints with Enterprise Manager design, and establish a database chart (in Chapter 15, in Chapter 15, which describes how to establish a database chart) in the case of the Foreign Key condition constraint. When using Enterprise Manager to create a new information list or edit the existing information list, the design information table window will appear. To establish a new information table, expand the servo and database data clips in the left pane of Enterprise Manager, press the right button on the information table, and select the new / data table from the quick function table. To display the design information table window of the existing information sheet, first find the expanded information table in the left pane, press the right button on the information table name in the right pane, then select the design data sheet from the fast display. Allow NULL values To specify whether the NULL value is allowed in the data line, it is possible to simply select or clear the appropriate check squares in the design data table window. You can set this option when you build a data sheet or modify the information list. See Chapter 10 of the rules that allow NULL values. Figure 16-11 shows the design information sheet window of the Customer Data Sheet of the Customer Table of We used to build and modify data sheets. You can see the two data lines of MID_INIT and CUST_PHONE allow NULL values, but other three-fund lines do not allow NULL values. Figure 16-11 Customer Data Sheet Design Data Sheet Window "Allow NULL" data line settings Unique To use Enterprise Manager to establish and modify the condition constraint, follow these steps: In the Design Table Window, select the Data Destroy Property button (button on the right side of the Archive button) in the tool column, select Index / Index in the Properties dialogue Key tab. Figure 16-12 shows the index / index key tab in the properties dialogue of the Customer Data Sheet. Use the following command to create this data sheet. The SSN data line of the data sheet includes a Unique condition constraint as an indexed index (SQL Server automatic replacement index is UQ_CUSTOMER_398D8EEE; you should now understand the benefits of conditional constraints and indexes): Create Table Customer ( First_name char (20) Not null, MID_INIT Char (1) NULL, Last_name char (20) Not null, SSN Char (11) Not Null Unique Clustered, Cust_phone char (10) NULL ) Go Figure 16-12 Index / Index Key Tabs in the Properties Dialogue of the Customer Data Sheet To create a new Unique condition constraint, press the new button on the index / index key tab of the property dialog box, select the information line name of the conditional constraint, type the new condition constraint name, then select Create a unique-UNIQUE core Square block. If you want to make it as a cluster index of the data line, select the setup of the cluster-Clustered check box in the data sheet and specify the fill factor. If you do not periodically automatically calculate the index fundament, select the option next to the selection. You can use the properties dialog to modify the UNIQue condition constraint. For example, modify the conditional constraint name, specify the information line to be added to the conditional constraint, set the clustered index option, and select the number of fills for the index. (Fills the factor will be described in detail on Chapter 17.) Conditional constraints, press the shutdown button after completing, and then press the Save button in Enterprise Manager to save the modification. Main index key You can specify a primary key condition for one or more data rows. Follow these steps to specify a Primary Key condition constraint: In the design data sheet window, press the storage grid in a column to select a list, or hold down the CTRL key, press the gray square on the left side of the data line to select multiple data at the same time. Press the right button on the selected data line to select the set main index key in the quick function table. Set a small key in the left side of the data set to the main index key. After setting the SSN data to the primary index key, the displayed window is shown in Figure 16-13. We have also deleted the UNIQUE condition constraint for SSN data lines, as it does not need to have UNIQUE conditions constraints and Primary Key conditions. Figure 16-13 Setting a Primary Key Condition Constraint in Design Data Sheet Window If you want to move the Primary Key condition to another, you only need to set the new data line to the main index key. You don't have to remove the original main index, SQL Server will delete and re-establish the Primary Key index. You can also modify the Primary Key index in the properties window. After the save button of the tool column is stored, the modification will take effect. Note If you modify the Primary Key condition constraint in the data sheet containing the information, re-establish the index may take a certain time. If the data table contains a large amount of information, you want to make a large modification of the index, such as changing the information line or a cluster state, preferably in the non-use peak period of the database. External index key To create or modify the Foreign Key condition constraint using Enterprise Manager, you can use the design information table window or create a database chart for associated with the external index key. It is best to establish an external index key correlation when establishing a data sheet (or at least before the data insertion data sheet). The following example will explain this reason. First, learn how to use the design information table to establish a Foreign Key condition constraint. We will use the two database information tables Items and Inventory established in front of this chapter to set the key to the external index key. We will re-establish an Item data sheet with primary key conditions (this is our previous use), but this Item_id data has no Identity nature. Because we need an example of Item_ID that has been updated, and the data of the Identity nature needs more steps to update. We will also re-establish an Inventory data table without Foreign Key conditions, so that we will join Foreign Key condition constraints later. To establish the above two data sheets, please refer to the following statements: CREATE TABLE ITEMS ( Item_name char (15) Not null, Item_id Smallint Not Null, Price Smallmoney Null, Item_Desc varchar (30) Not null default 'none', ConsTRAINT PK_ITEM_ID PRIMARY Key (item_id) ) Go CREATE TABLE Inventory ( Store_ID Tinyint Not Null, Item_id Smallint Not Null, Item_quantity Tinyint Not Null ) Go To increase the Foreign Key condition constraint in the Inventory table, follow these steps: Press the right button on the Inventory data table name of the ENTERPRISE Manager, select the design data sheet. And press the right button in the blank part of the window, select the association tab to enter the associated tab, as shown in Figure 16-14: Figure 16-14 Relevance tab within the properties window in the Inventory table Press the new button, the preset value is displayed in the window, as shown in Figure 16-15. In this example, we choose Items in the main index key table and select the item_id data line as an external index key of the Items and Inventory sheets. As long as you press the blank data line below the information table name just selected, a drop-down option will appear. When the information table to be associated is elected, the contents of the associated name will change, as shown in Figure 16-16. Figure 16-15 Press the "Relevance" tab in the new "Relation" tab Figure 16-16 "Relevance" tab Displays external index key association with Items and Inventory data sheets There are several verification blocks below the window, and the example option is shown in Figure 16-17. To check the existing information, you must check the existing data option when you check the existing information according to the association of the external index key. At this time, the information is not in conformity, and the establishment of the conditional constraint will fail. You can also choose to clear this block without checking existing information. But if you want to update or delete the existing columns, you will have trouble. Figure 16-17 "Relevance" tab shows the options already selected The next option is a replication action will use this association. Do not select this option if you don't write. Even if you want to reply, it doesn't matter, it doesn't need to be selected, because the information in the original data sheet is checked according to the conditional constraint, so it is no longer reviewed. If you still choose to enable, in the case where the two associated data sheets cannot cooperate with each other, the error message will be received when the replication is written, telling you that the action of the copy data column is constrained by the external index key condition. Unable. Next is the insertion and update action will use this association option. Choosing this option indicates that the Foreign Key condition constraint will be viewed at the same time when inserting, updating, and deleting actions. The two logging blocks below will be selected. Selecting the series update correlation field indicates that the external index key data table is also updated when the data row referenced in the reference table (for example: updating the item_ID value of the items data sheet), will also update the external index key (for example: inventory data sheet) Item_ID data line will be updated at the same time - in series update). In the external index key data sheet, the information on the remaining information will not change in addition to the information. If you do not select this option, you will not be able to update the external index key of the external index key data sheet, SQL Server transmits an error message as follows: "Update statement is constrained to each other with the Column Reference condition of 'fk_inventory_items'. This conflict is generated in the 'MyDB' Database, 'Inventory' Data Sheet, 'Item_ID' Data Line. Therefore, the statement has been terminated. "Selecting the series delete related record indicates that the external index key information is deleted in the rending action in the reference table. Information in the table - Tandem deletion. For example, if you delete the information columns in the Items data table, the information column with the same Item_ID value in the Inventory table will also be deleted. This action will keep your information consistency. If you do not choose this item, you cannot update the information line referenced by the external index key data sheet. SQL Server transmits a following error message: "Delete statement is constrained to each other with the Column Reference condition of 'fk_inventory_items'. This conflict is generated in the 'MYDB' database, 'Inventory' data table, 'Item_ID' data line. Therefore, this statement The style has been terminated. "After the selection is completed, press Turn off in the Design Table window, then press the Save button to store the change. Another window will display, tell you that the data list will be stored to the database and display the association of the external index key of these two data sheets. After the completion, press OK, then press the top right of the design data table (non-Enterprise Manager window, or Enterprise Manager will turn off). Another way to build and modify the FOREIGN Key condition constraint is to use the database chart. To learn how to use the database graph to establish and modify the Foreign Key condition constraint, we will use the two data sheets used by the previous example: Items and Inventory data tables to create a chart. First take a look at the database chart without external index key, then add external index key correlation. The initial database chart is shown in Figure 16-18. Figure 16-18 Database chart of Items and Inventory sheets As shown in Figure 16-18, the item_id data line of the Items data sheet is the main index key data line. It is a unique candidate index key that can be used for external index key (because there is no other UNIQue condition constraint). To establish an external index key association between the Item_ID data line of the ITEMTORY table, the external index key association between the Item_ID data line of the Items data sheet is performed: Press and hold the left side of the column indicating the Items data sheet item_ID data line (gray square block), Drag the indicator to the Inventory data sheet (you can see that there is a dashed line behind the indicator). Loosen the mouse button when the indicator points to the Item_ID session of the Inventory data sheet. The setup of the associated dialogue is displayed, as shown in Figure 16-19. This dialogue is similar to the properties window of the previously shown design information table window. In the dialogue, the data sheet will display the Item_ID data line, indicating that the external index key association will be established between the Item_ID data line between the two data sheets. Figure 16-19 "Establishing the Relationship" dialogue displaying the proposed external index key correlation You can change the associated name, or you need to select or clear the options below the dialog. These options have been introduced for you before. Press to determine the graphic connection of two data sheets that represent external index keys, as shown in Figure 16-20 (this step is not stored), there will be a small key to connect from the external index key data sheet to the main index key information. table. Press the Save button to save the modification. Please enter the name of the database chart and confirm the changes to the relevant information sheet. Then press it to be determined. Figure 16-20 Displays a database chart showing an external index key. To modify an externally index key, use two methods described in this chapter. In the design data sheet window, you can save the modification as long as you turn on the associated tab again. In the database chart, press the right button on the associated line of the chart, select the properties to update the condition constraint, or select the full deletion of the conditional constraint from the database delete association. Then add a new one in your needs. CHECK To establish a check condition constraint using the design information table window, open the design information table window of the information sheet you want, and then follow the steps below: Press the right button in the window to select the property window from the flash function table. Press to check the condition constraint tab and press to increase, as shown in Figure 16-21. Next, enter an operational calculation used to verify insert or modify the data. In our example, the Price data of the Items data sheet adds a Check condition constraint, only between $ 1.00 and $ 1000.00, can be inserted into the data sheet, as shown in Figure 16-22. It is noted that three selected squares below. The first: Select the selection of the data option immediately, indicating that all materials that already exist in the data sheet must be reviewed by the Check condition. Conditional constraints cannot be established without compliance with the Check Conditional Constraint. The second: Selecting the copying action must be reasonable to constrain, indicating that when the existing information is represented, because this information has been checked, the data is written, so the data does not need to be reviewed again. The third: Selecting insertion and update actions must be reasonable to constrain, indicating that the CHECK condition is constrained. If this option is not selected, the conditional constraint will still be set, but there is no setting to take effect, so the Check condition constraint does not work. Figure 16-21 "Check Condition Constraint" tab in the "Properties" window Figure 16-22 CHECK condition constraints for new Price Data in the Items Data Sheet To change the Check condition constraint, you must check the Name, analog, and options to the Check Condition Constraint Tab. The CHECK condition constraint for the Price Data Row is displayed from 1 to 1000 to 1 to 99. At this point you should be able to see the selection square that immediately checks the existing data option immediately when it is created, because the condition constraint has been set before. To verify the existing information, you should select this option based on the scope of the change. If the verification of existing materials fails, the error message will be obtained, and the range of updates cannot be established. You can also select the condition constraint you want to delete in the selected condition constraint drop-down list, just click the Delete button to remove the condition constraint on the check condition constraint tab. Another way of constraints of rule objects use CHECK condition is to establish rule objects to limit numerical ranges inserted and modified in the data line. Rule objects are similar to the preset object, which is established separately from the data sheet, and the rule object is also deleted at different times when deleting the data sheet. You must establish a rule object to be tied to the data line or the user's customization, this example uses the sp_bindrule system pre-depreciation program. As with the preset object, the rule of the SQL Server 2000 reserves the regular object is for later compatibility. The use of Check condition constraints is the best way to the numerical range in the conditional constraints, but the rule object is more convenient when you need the same rules for many data lines or users. Rule objects with T-SQL, for example, let us establish a rule object that has the same functionality with Check condition constraints. Rules use variable name @price to reference the Price Data Line of the Items Data Sheet. Variable names must start with @ symbols, but you can arbitrarily select variable names. First we will establish rules, then set it to the information line, as shown below: Use mydb Go CREATE RULE price_rule as (@Price> = .01 and @price <= 500.00) Go sp_bindrule "price_rule", "items.price", "Futureon" Go To unite and delete the association, use the following statement: sp_unbindrule "items.price" Go DROP Rule Price_rule Go The parameters of sp_bindrule and sp_unbindrule are the same as the parameters of sp_bindefault and sp_unbindefault (introduced in the Create default and sp_bindefault part in front of this chapter). Although you can assign a rule and more than one Check condition constraint on the same data line or user's custom data type, each data line or a user's custom can only have a rule. If you do this, SQL Server will set all the conditions to all the conditions for inserted or modified. Establishing rule items using Enterprise Manager follows the steps below, using Enterprise Manager, and sets the rules of the rules: Expand the servo and database name in Enterprise Manager, press the right button on the rules, select the new rule from the quick function table, display the rule properties dialogue. In this example, we name the rules Price_rule and add a text, as shown in Figure 16-24. Press to determine the completion of the rule object. To serve the rules, press the rules in the left pane of Enterprise Manager, press the right button on the new rule name, and select the property display rule properties window from the quick function table. Like the previous preset object, press the UDT button to set the rule to the user's custom data type; or press the line of data to set the rules to the data line. In this example, the line of the Items data sheet is selected in this example, as shown in Figure 16-25. Figure 16-24 "Rule Properties" Window Diagram 16-25 of Rule 16-25 Press to determine the set rules, then press OK to close the rule properties window. To delete a rule, you must first remove the rules from the information line or the user's custom data type. After removing the rules, press the right button on the rule name, select the delete from the quick function table, and press all the removed items in the Remove the object dialog. If the rules you want to delete are tied to any item, SQL Server will have an error message indicating that the rule object cannot be deleted. Summary of this chapter In this chapter, we have learned five types of preset values and conditional constraints that can be applied to data lines or data sheets, and how to build and modify preset values and conditional constraints using T-SQL commands and Enterprise Manager. At the same time, I have learned how to use preset values and rule objects to establish and modify preset values and rules. If no specific value is assigned, the preset value provides a way to specify a preset value for the data row. Conditional constraints provide several different ways to maintain data integrity in the database. If you want to set preset values and conditional constraints in the database data sheet, you will find that they are very useful tools. Chapter 17 will learn how to use SQL Server indexes, including clustered indexes, and non-clustered indexes, because indexing can greatly increase the efficiency of data access.
